Download BTEC Level-4: Database Assignments and more Study notes Computer science in PDF only on Docsity!
Part 1 Design a relational database system using appropriate design tools and techniques, containing at least four interrelated tables, with clear statements of user and system requirements.
Introduction This first part of the assignment, a design or the configuration of the database system for the QFX is provided by me as I am hired as the IT manager for this project, and I have document ed the different tools and techniques used to complete the project. The total design or the framework is provided by this document that uses the required and the suitable tools and techniques of the database system. Database configuration or the design is the way toward creating information model of a database. A database configuration begins with a rundown of information that we need to remember for the database and what we will do with the database later. A database, which is appropriately configuration, will offer us with access to forward-thinking, exact data in light of the fact that right structure is significant in succeeding our objective in working with a database, contributing the time required to become familiar with the standards of good plan that promises well. Entity relationship Diagram The entity relationship diagram is also known as the ER diagram. The ER diagram is the graphical diagram that provides the information and the relation between different objects such as the places, objects, events etc. “Entity Relationship Diagram, also known as ERD, ER Diagram or ER model, is a type of structural diagram for use in database design. An ERD contains different symbols and connectors that visualize two important information: The major entities within the system scope, and the inter-relationships among these entities.” (Visualparadigm, 2018) Different symbols are used, and they have specific purpose, the symbols like the rectangle, diamond and ovals are used and they perform differently. The symbols used in the ER diagram are explained below.
- Entity The class, place or any object can be considered as an entity. The rectangle is used in the representation of the entity. The entities are also known as the tables and are used in the storage of the data. The entities like the admins, beacons, clients, ads etc. are used in the beacon management system.
Relationship between entities The situation among two social database tables when one table has a foreign key that mentions the essential/ primary key of the other table. It shows how the information or data is shared between the substances. It permits discerning database to part and store information in various tables, while connecting various information things. The three types of relationships are given below: One to one This is the type of relationship or the connection between the two tables where there is end- end connection. The value of the primary key has the relation with one or none of the tables. Fig: Example of one to one relationship One to many When the one row of the initial table is in link with many rows of the secondary table then such relationship is the one to many relationships. Fig: Example of one to many relationship Many to many This is that sort of relationship that forms when the records in any tables relate to many records of the other table. If there is the formation of the many to many relations, then the relation must
be broken down into one to one or one to many relations by the usage of the bridge table. The composite primary key is formed from the bridge table in this type of relationship. Fig: Examples of many to many relationship
Following figure shows the schema diagram of the beacons management system. Fig: Schema diagram Data dictionary “A Data Dictionary provides detailed information about the business data, such as standard definitions of data elements, their meanings, and allowable values. While a conceptual or logical Entity Relationship Diagram will focus on the high-level business concepts, a Data Dictionary will provide more detail about each attribute of a business concept. Essentially, a data dictionary provides a tool that enables you to communicate business stakeholder requirements in such a way that your technical team can more easily design a relational database or data structure to meet those requirements. It helps avoid project mishaps such as requiring information in a field that a business stakeholder can’t reasonably be expected to provide or expecting the wrong type of information in a field.” (Brandenburg, 2017)
Field name The field name is the name that helps in the identification of the table in the database. This name keeps all the detail on each of the table. Attribute Also known as the distinctive identifier it helps in the description of the behavior of the table. It also helps in the description of the elements that are described the data dictionary. The attributes can only be modified and added by the person with the administrative privilege. Data type What sort of data is to be allowed in the field is defined by the data type, some of the common data types are the integers, Varchars, date etc. Length The range of the data type is described by the length in the database. 0-255 is the fived length of the VARCHAR whereas the TEXT has 65,535 characters in it. Constraints The rules in the table for the data are specified by the constraints. For the limitation of the data that must be given into the table is done by the constraints. Some of the examples of the commonly used constraints are NOT NULL, NULL, PRIMARY KEY etc. Description The action of defining the attribute is called as the description. It is done so that the attributes are easily understood. Table name Attribute Data type Length Description Example Id Integer 10 Primary key 101 Name Varchar 255 Admin’s name Kapil Bam
Beacons Id Integer 10 Primary key 301 Title Varchar 255 Beacon’s Title QFX mall U-id Integer 10 Id of beacon 001 Description Text N/A Details Push notifications Proximity Integer 10 Area that the beacon covers
Area_id Integer 10 Foreign key 501 Table name Attribute Data type Length Description Example Clients Id Varchar 10 Primary key 401 Name Varchar 255 Client’s name Subash Shrestha Address Varchar 255 Client’s address Balkot Email Varchar 255 Client’s email [email protected] Mobile number Varchar 15 Client’s number
Table name Attribute Data type Length Description Example Id Integer 10 Primary key 601
Impressions Date Date N/A Impression’s date
Time Time N/A Impression time
Ads_id Integer 10 Foreign Key 201 Table name Attribute Data type Length Description Example Clicks Id Integer 10 Primary key 701 Date Date N/A Click’s date 17/23/ Time Time N/A Click time 12:15: Ads_id Integer 10 Foreign Key 201 Requirements User requirements Being the QFX’s main IT head office we have been assigned a task to make a database system for the beacon management system and has listed some of the requirements that they need for this project. According to them this management should be able to perform the following activities: I) The system must be able to store the details of the admin and the clients. II) The system must be able to store the ads. III) The system must be able to keep records of the area along with the beacon details. IV) Finally, it must also be able to keep the perfect records of the impressions and the clicks. So, these were the major requirement of the beacon management system that the QFX requested. System requirements Being simpler yet flexible the system should use the SQL for the manipulation of the data and Apache Server must also be used.
Part 2 Develop the database system with evidence of user interface, output and data validations, and querying across multiple tables. Implement a query language into the relational database system. Test the system against user and system requirements.
Introduction In this part of the assignment I will provide the document that gives the fully functioning system on the design that was provided in the previous part. This part will include all the functionalities such as the evidence of user interface followed by the validations and the output as well. These all will be provided using the different tables. Finally, I will be discussing about the query language used in this database system. Database constraints Database requirements or the constraints are the standards proposed on the information section of a table to meet the information respectability (data integrity). As far as possible the kind of information that can be embedded, refreshed or erased from a table. The fundamental reason for imperatives is to keep information exactness and dependability in database. The most ordinarily utilized requirements/ constraints are: Unique Key constraint The standard that forestalls the duplicate values in segment inside a table guaranteeing one of a kind incentives/ values for each column. The sections expressed in an exceptional imperative must be characterized as NOT NULL. Information ought not rehash or repeat, and its uniqueness should assist with recognizing the information. Primary key constraint The uniqueness of the primary key that records the data in the database does not allow any table to be null. The primary is very distinctive in nature. For a table to have the relationship with its child table the primary key should be used as it uniquely identifies the relationship.
Joins “The SQL Joins clause is used to combine records from two or more tables in a database. A JOIN is a means for combining fields from two tables by using values common to each.” (Tutorialpoints, 2016) The two or more than two tables are joined in the SQL by using the join keywords. Types of joins are given below: Inner join It is a voluntary part of the SELECT statement and its job is to return all the identical data from the tables. Example: Left join The main objective of the left join is to return the data from the left side of the table along with the matching data from both tables.
Example: Right join The main objective of the right join is to return the data from the right side of the table along with the matching data from both tables. Example:
