Download Assignment 1 - 1622 Database and more Study Guides, Projects, Research Computer Programming in PDF only on Docsity!
ASSIGNMENT 1 FRONT SHEET
Qualification TEC Level 5 HND Diploma in Computing Unit number and title Unit 04: Database Design & Development Submission date 22 - 10 - 2022 Date Received 1st submission Re-submission Date Date Received 2nd submission Student Name Student ID Class Assessor name Student declaration I certify that the assignment submission is entirely my own work and I fully understand the consequences of plagiarism. I understand that making a false declaration is a form of malpractice. Student’s signature Grading grid
P1 M1 D
Summative Feedback: Resubmission Feedback:
Grade: Assessor Signature: Date: Signature & Date:
- Chapter 1 - Statements of user and system requirements
- Overview about the Problem
- Requirements of the application
- Chapter 2 – Design the relational database system
- Analyze the requirements
- Database design with explanations................................................................................................
- Review whether the databased is normalized
- Data validation
- References
- Figure 1: Entity Relationship Diagram for Hotel Management Table of Figures
- Figure 2:Illustration 'Chefs' table
- Figure 3: Illustration 'Dishes'' table
- Figure 4: Illustration 'Waiters' table
- Figure 5: Illustration 'Tables' table
- Figure 6: Illustration 'Customers' table.....................................................................................................
- Figure 7: Illustration 'Bills' table
- Figure 8: Illustration 'Orders' table
- Figure 9: Illustration ERD ‘Chefs’ and ‘Dishes’......................................................................................
- Figure 10: Illustration ERD ‘Tables’ and ‘Bills’......................................................................................
- Figure 11: Illustration ERD ‘Customers’ and ‘Bills’
- Figure 12: Illustration ERD ‘Waiters’ and ‘Bills’
- Figure 13: Illustration ERD ‘Bills’ and ‘Orders’
- Figure 14: Illustration ERD ‘Dishes’ and ‘Orders’
- Figure 15: Illustration for Normalization
- Figure 16: Illustration for INT data type
- Figure 17: Illustration for NVARCHAR data type
- Figure 18: Illustration for DATETIME data type
- Figure 19: Illustration for IDENTITY....................................................................................................
- Figure 20: Illustration for CHECK in 'Orders' table
- Figure 21: Illustration for CHECK in 'Chefs' table
- Figure 22: Illustration for NOT NULL
Chapter 1 - Statements of user and system
requirements
1. Overview about the Problem
➢ Da Nang city still has and continues a rapid development of tourism nowadays, and the demand for services is increasing gradually, so food and beverage services are indispensable. However, some restaurants have faced some quandaries due to that high- speed development. Thus, to make a restaurant management easier and more productive, this restaurant management business was embarked on. In addition, the main purpose of this project is to create a database management system for a restaurant. In this project, restaurant the restaurant administrator can manage the customer flow by giving them the ability to order dishes from the menu.
2. Requirements of the application
Restaurant management system has 4 roles: Restaurant administrator, chef, waiter and customer
- As a restaurant administrator: o I should be able to update information about my restaurant o I should be able to view weekly sales of my restaurant o I should be able to observe current orders from customers
- As a chef: o I should be able to view the current queue of orders o I should be able to accept the orders
- As a waiter: o I should be able to view the current queue to see the ready orders for pickup o I should be able to mark the orders as picked up on the common queue o I should be able to view the feedback of customers concerning the meal and service o I should be able to take payment from the customer and thus mark the bill as paid
- As a customer: o I should be able to browse through the menu and look at the various food options available in the restaurant along with the price for each item. Functionalities of restaurant management system:
- Create: o Create a new bill when customers have a table o Create new information for a new employee to work at restaurant
- Read: o Read information of employees, customers, orders, menu
- Update:
Entity Relationship Diagram for Restaurant Management Figure 1 : Entity Relationship Diagram for Hotel Management Explain for Entity Relationship Diagram ➢ This diagram includes 7 tables with each separate attribute in each table.
- ‘Chefs’ table: The ‘Chefs’ table contains the primary key of the id (of the chef). Besides, the ‘Chefs’ table also has 3 attributes to contain chef information such as Firstname, Lastname, Gender, Phonenumber, DoB (Date of birth). Figure 2 :Illustration 'Chefs' table
- ‘Dishes’ table: The ‘Dishes’ table contains the primary key of the id (of the dish), foreign key of ChefsId. In addition, the ‘Dishes’ table has 2 attributes to hold the dish information such as DName and DPrice.
Figure 3 : Illustration 'Dishes'' table
- ‘Waiters’ table: The ‘Waiters’ table contains the primary key of the id (of the waiter) Besides, the ‘Waiters’ table also has 3 attributes to contain waiter information such as Firstname, Lastname, Gender, Phonenumber, DoB (Date of birth). Figure 4 : Illustration 'Waiters' table
- ‘Tables’ table: The ‘Tables’ table contains the primary key of the id (of the table). Besides, the ‘Tables’ table also has 1 attribute to contain table information such as Name. Figure 5 : Illustration 'Tables' table
- ‘Customers’ table: The ‘Customers’ table contains the primary key of the id (of the customer). Besides, the ‘Customers’ table also has 3 attributes to contain customer information such as Firstname, Lastname, and Phonenumber Figure 6 : Illustration 'Customers' table
- ‘Bills’ table: The ‘Bills’ table contains the primary key of the id (of the bill), foreign key of TablesId, CustomersId, and WaitersId. In addition, the ‘Bills’ table has 2 attributes to hold the bill information such as Date_time, and Totalprice. Figure 7 : Illustration 'Bills' table
- ‘Orders’ table: The ‘Orders’ table contains the primary key of the id (of the order), foreign key of BillsId and DishesId. In addition, the ‘Orders’ table has 1 attribute to hold the order information such as Quantity. Figure 8 : Illustration 'Orders' table
Figure 11 : Illustration ERD ‘Customers’ and ‘Bills’
- The relationship between ‘Waiters’ table and ‘Bills’ table ➢ Based on the below ERD, a waiter can take payment in multiple bills, however, one waiter can exist in a bill at a time developed on ‘Waiters_Id’. Therefore, the above relationship is called a One-to-Many relationship. Figure 12 : Illustration ERD ‘Waiters’ and ‘Bills’
- The relationship between ‘Bills’ table and ‘Orders’ table ➢ According to the below ERD, an invoice might contain many orders for dishes in a previously set up dishes list. Thus, the above relationship is called a One-to-Many relationship.
Figure 13 : Illustration ERD ‘Bills’ and ‘Orders’
- The relationship between ‘Dishes’ table and ‘Orders’ table ➢ According to the below ERD, an order will contain a dish in the restaurant’s dishes list. A dish can available at multiple orders, and each time placing multiple orders with the same dish, the relationship between ‘Dishes’ and ’Orders’ is a One-To-Many Figure 14 : Illustration ERD ‘Dishes’ and ‘Orders’ Data types
- ‘Orders’ table: Orders Column name Data type Id INT Bills_Id INT Dishes_Id INT Quantity INT
- Review whether the databased is normalized Problems without normalization: When a table has superabundance data and is not correctly normalized, the database will be difficult to control and update, without facing data loss. Not only that, it also will consume additional storage space. There are 3 main oddments:
- Insert anomaly
- Update anomaly
- Delete anomaly It is useful to split large database tables into smaller tables and join them together. It can be simply to add, change, remove table fields and decrease superabundance data. Objective of normalization:
- Remove duplicate data and database anomalies from relational tables.
- Reduce redundancy and complexity by exploring new data types used in tables.
- Split a large database table into smaller tables and joins them in relationships.
- Avoid duplication of data and repetition of groups in the table.
- Reduce chances of database anomalies Normalization rules are decomposed into the following normalized form:
- First Normal Form (1NF): Relationships with complex or multi-valued attributes are in their first normal form, and relationships without complex or multi-valued attributes are also in their first normal form. A relation is in its first normal form if all attributes in the relation are single-valued attributes.
- Second Normal Form (2NF): The principle of perfect functional dependence underlies Second Normal Form (2NF). Second normal form is used for composite key relationships, which are master keys made up of two or more attributes. Single-attribute primary key relationships are in at least 2NF by default. Update anomalies can affect relationships that are not in 2NF.
- Third Normal Form (3NF):
If a non-prime attribute has no transitive dependencies and the relationship is in second normal form, it is in third normal form. Normalize for design Figure 15 : Illustration for Normalization ( Source: https://tutorials.ducatindia.com/wp-content/themes/asb-ducat- tutorials/images/dbms/what-is-normalization-1.jpg ) ➢ UNF to 1NF:
- Specifies an attribute or set of attributes as keys for a denormalized table.
- Identifies repeating groups for key attributes in denormalized tables.
- Remove repeating group by: o Populate empty columns in rows with repeated data ("flatten the table") ➢ 1NF to 2NF: ➢ 2NF to 3NF:
- Data validation Tables Attribute Constraint Explain Chefs Id PK, INT The column value as the primary key must be unique and not duplicated when used to set the primary key on the table. The columns have to be NOT NULL when declaring a primary key constraint. FirstName NVARCHAR(10) Used to save Unicode data. LastName NVARCHAR(25) Used to save Unicode data. Gender VARCHAR(7) Used to save data
Customers_Id FK, INT Used to create a foreign key on a table that references another table through the value of a connected column. The value of the connected column must be unique in the other table. Used to save data as integer. Waiters_Id FK, INT Used to create a foreign key on a table that references another table through the value of a connected column. The value of the connected column must be unique in the other table. Used to save data as integer. Date_time DATETIME Used to save date and time data. Totalprice INT Used to save data as an integer Orders Id PK, INT The column value as the primary key must be unique and not duplicated when used to set the primary key on the table. The columns have to be NOT NULL when declaring a primary key constraint. Bills_Id FK, INT Used to create a foreign key on a table that references another table through the value of a connected column. The value of the connected column must be unique in the other table. Used to save data as integer. Dishes_Id FK, INT Used to create a foreign key on a table that references another table through the value of a connected column. The value of the connected column must be unique in the other table. Used to save data as integer. Quantity INT Used to save data as an integer Data type:
- INT: Data type Range Storage INT - 2,147,483,648 to 2,147,483,647^ 4 bytes The primary integer data type is INT. Up to about 2 billion can be stored. This is large enough to store a lot of numeric data. This may be a TINYINT if a value less than 255 is expected. Note that the word INTEGER is a synonym for INT. In other words, if we use an INTEGER, the database will convert it to an INT. In this restaurant management system, INT data type is used for Id, Price, Quantity, etc
Figure 16 : Illustration for INT data type
- NVARCHAR: NVARCHAR (for variable character) is a flexible-width Unicode data type. The syntax for declaring a NVARCHAR variable is NVARCHAR(n), where n specifies the size of the string in byte pairs. The value of n must be from 1 to 4000. The memory taken by NVARCHAR is always (2 * n) bytes + 2 bytes. 2 extra bytes to store the length information of the string. In this restaurant management system, NVARCHAR data type is used for Id, Price, Quantity, etc Figure 17 : Illustration for NVARCHAR data type
- DATETIME: In SQL, the datetime data type is used for values that contain both dates and times. Microsoft defines it as a date associated with a time of day with fractional seconds based on a 24-hour clock. SQL in particular has many data types that combine both date and time representations, which makes things more complicated. The most widely used is DATETIME because it has been around since earlier versions of SQL. SQL retrieves and displays the DATETIME values in the format 'YYYY-MM-DD hh:mm:ss'. The supported range is '1753- 01 - 01 00: 00:00' to '9999- 12 - 3 23:59: 59,997'. In this restaurant management system, DATETIME data type is used for Date_time
Figure 20 : Illustration for CHECK in 'Orders' table Constraint ‘Genders’ by CHECK In the 'Chefs' table, we use a CHECK constraint for 'Genders' attribute to prevent the genders to be two genders for this column. Therefore, the input data is only is two genders (‘Male’ & ‘Female’). Figure 21 : Illustration for CHECK in 'Chefs' table
- NOT NULL: The NOT NULL constraint forces a column not to accept NULL values, which means we cannot insert or update a record without adding a value to that field. Furthermore, it is applicable to any datatype. Figure 22 : Illustration for NOT NULL
References
Anon, n.d. nchar, nvarchar & Unicode data types in SQL Server. [Online] Available at: https://www.tektutorialshub.com/sql-server/nchar-nvarchar-unicode-data-types-in-sql- server/ [Accessed 19 10 2022]. Anon, n.d. Normalization of Database. [Online] Available at: https://www.studytonight.com/dbms/database- normalization.php#:~:text=Problems%20Without%20Normalization,database%2C%20without%20fac ing%20data [Accessed 18 10 2022]. Anon, n.d. Purpose of Normalization. [Online] Available at: https://www.javatpoint.com/dbms-purpose-of-normalization [Accessed 18 10 2022]. Anon, n.d. SQL NOT NULL Keyword. [Online] Available at: https://www.w3schools.com/sql/sql_ref_not_null.asp [Accessed 19 10 2022]. Anon, n.d. SQL Server: Check Constraints. [Online] Available at: https://www.techonthenet.com/sql_server/check.php [Accessed 19 10 2022]. Brumm, B., 2022. SQL INT Data Types in Different Databases. [Online] Available at: https://www.databasestar.com/sql-int-data-types/ [Accessed 19 10 2022]. Choudary, A., 2022. SQL Datetime: Everything you Need to Know. [Online] Available at: https://www.edureka.co/blog/sql-datetime/ [Accessed 19 10 2022]. Larsen, G., 2021. SQL Server identity column. [Online] Available at: https://www.red-gate.com/simple-talk/databases/sql-server/learn/sql-server-identity- column/ [Accessed 19 10 2022].
