Download Assignment 2 Database Management Systems and more Assignments Database Management Systems (DBMS) in PDF only on Docsity!
ASSIGNMENT 2 FRONT SHEET
Qualification TEC Level 5 HND Diploma in Computing
Unit number and title Unit 04: Database Design & Development
Submission date Date Received 1st submission
Re-submission Date Date Received 2nd submission
Student Name Phan Minh Tri Student ID GCD
Class GCD0904 Assessor name Do Duy Thao
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
P2 P3 P4 P5 M2 M3 M4 M5 D2 D
Summative Feedback: Resubmission Feedback:
Grade: Assessor Signature: Date:
Signature & Date:
Table of Contents
- CHAPTER 1 – Develop the database system
- I. Final Mock-up of the application
- II. Queries to create database with results
- III. Data Validation
- CHAPTER 2 – Produce Queries
- I. Queries to INSERT data with illustration of final result...................................................................................
- II. Queries to SELECT data with illustration of final result...................................................................................
- III. Queries to UPDATE data with illustration of final result
- IV. Queries to DELETE data with illustration of final result
- V. 5 Advanced Statistical Actions.........................................................................................................................
- Summarize all information orders throughout the day
- Statistics of 2 orders with the highest value in month................................................................................
- Statistics of 2 orders with the lowest value in month
- Show list of books in the online store
- Sales statistics for the certain day
- CHAPTER 3 – Test the system
- Figure 1: Example of Book Store's website Table of Figures
- Figure 2: Create new database: BookStore
- Figure 3: Create Customer Table
- Figure 4: Create Book Table
- Figure 5: Create Inventory Table
- Figure 6: Create OrderDetail Table
- Figure 7: Create Orders Table
- Figure 8: All tables in the database after being created
- Figure 9: The database diagram (ERD)
- Figure 10: Example of Integer
- Figure 11: Example of Nvarchar
- Figure 12: Example of DATE
- Figure 13: Example of Presence and Uniqueness
- Figure 14: Example of JOIN command
- Figure 15: The result of JOIN command
- Figure 16: Insert data for Book Table
- Figure 17: Insert data for Customer Table
- Figure 18: Insert data for Inventory Table
- Figure 19: Insert data for OrderDetail Table
- Figure 20: Insert data for Orders Table
- Figure 21: Example of printing information in Customer table
- Figure 22: Print all information of Book table
- Figure 23: Print all information of Customer table
- Figure 24: Print all information of Inventory table
- Figure 25: Print all information of OrderDetail table
- Figure 26: Print all information of Orders table
- Figure 27: Orders's Table Before UPDATE
- Figure 28: Apply command UPDATE into Orders Table
- Figure 29: Command SELECT to print all information of Orders Table
- Figure 30: The Orders Table after UPDATE
- Figure 31: Inventory Table before DELETE
- Figure 32: Command DELETE to remove specific information of Inventory Table
- Figure 33: Inventory Table after DELETE
- Figure 34: Print all information of orders in month by SELECT and JOIN command
- Figure 35: The information of orders after being summarized
- Figure 36: The command to execute the function
- Figure 37: 2 Customer of highest value orders
- Figure 38: The command to execute the function
- Figure 39: 2 Customer of lowest value orders
- Figure 40: The command to combine 2 tables to execute the function
- Figure 41: All necessary information to manage Book in the store
- Figure 42: The command to sales statistics of January 1,
- Figure 43: All the information of the sale that day
II. Queries to create database with results
- At the beginning, I create new database first Figure 2 : Create new database: BookStore
- Then, I began to create all the necessary tables in the system on the newly created database Figure 3 : Create Customer Table Figure 4 : Create Book Table
Figure 5 : Create Inventory Table Figure 6 : Create OrderDetail Table Figure 7 : Create Orders Table
- This is the result after I create all tables Figure 8 : All tables in the database after being created
❖ DATE : This data is used when the attribute is the date, month and year. The format of this data
type is YYYY-MM-DD. For example, 2021- 01 - 01
Figure 12 : Example of DATE
❖ Presence : When the attribute in a table is marked with ‘ NOT NULL’ , it means this attribute must
be filled with data. If not, the table cannot be created. Data is mandatory. For example, books are
required to have information other than ID to be able to describe for that book. So, the
information like name, price, and so on are indispensable.
Figure 13 : Example of Presence and Uniqueness
❖ Uniqueness : Each table has a special attribute called the PRIMARY KEY. The data in this attribute
must be unique, and the data cannot be empty. The PRIMARY KEY constraint in the table helps
users to identify each record in a database system.
❖ Querying across multiple tables : In SQL Server, when users want to query across multiple tables,
they use the command JOIN. This function will merge tables that are linked together via
PRIMARY KEY – FOREIGN KEY constraints. For example, I want to print to the screen all the
information related to the book, but some of the information is outside the Book table. So, I will
use the JOIN command to combine with other tables and get information from those tables.
Figure 14 : Example of JOIN command
➢ This is the result
Figure 15 : The result of JOIN command
CHAPTER 2 – Produce Queries
I. Queries to INSERT data with illustration of final result
❖ After creating all the tables in the system, I use the command
<<INSERT + [Table Name] + (Attributes) + VALUES>> to insert data on each table, because
[Attributes] of table is optional, we can use simple command
<<INSERT + [Table Name] + VALUES>>
Figure 16 : Insert data for Book Table Figure 17 : Insert data for Customer Table Figure 18 : Insert data for Inventory Table
- Print all information of Book table Figure 22 : Print all information of Book table
- Print all information of Customer table Figure 23 : Print all information of Customer table
- Print all information of Inventory table Figure 24 : Print all information of Inventory table
- Print all information of OrderDetail table Figure 25 : Print all information of OrderDetail table
- Print all information of Orders table Figure 26 : Print all information of Orders table III. Queries to UPDATE data with illustration of final result
❖ To update data in the table, I will use the command
<<UPDATE + [Table Name] + WHERE + [Condition]>> , so I can change the table’s data by each
attribute or all the properties of that table based on the condition.
❖ For example, in the Orders table, the [TotalPrice] attribute is currently null. I will update price for
all orders in this table.
- The table before UPDATE Figure 27 : Orders's Table Before UPDATE
- The Inventory table before DELETE Figure 31 : Inventory Table before DELETE
- I use command DELETE to remove data which [Address] = ‘Da Nang’ Figure 32 : Command DELETE to remove specific information of Inventory Table
- The Inventory table after DELETE Figure 33 : Inventory Table after DELETE V. 5 Advanced Statistical Actions
- Summarize all information orders throughout the day
❖ This function of the program will make it easier for the manager to summarize all the information
of the orders such as the order’s information, the customer’s information and the value of that
orders in 1 day
❖ For example, I want the system show all information of all orders in January 1, 2021. I use the
SELECT command in combination with the JOIN command to be able to print to the screen all the
information of the orders from the linked tables.
Figure 34 : Print all information of orders in month by SELECT and JOIN command
- After applying the command to the system Figure 35 : The information of orders after being summarized
- Statistics of 2 orders with the highest value in month
❖ This function will help the manager to know which customer the order belongs to and there will be
gratitude programs for that customer such as giving a coupon for the next order, etc.
- I use the SELECT command in combination with the JOIN command to be able to print to the screen
all information of customers who has orders with highest value. I also use ORDER BY [TotalPrice]
DESC command to sort the values of the table from high to low according to [TotalPrice] attribute
and combined with TOP (2) command, the program will only take the first 2 values of the table and
the 2 highest values of the table printed to the screen.
Figure 38 : The command to execute the function
- After applying the command Figure 39 : 2 Customer of lowest value orders
- Show list of books in the online store
❖ This function helps the manager to manage books more easily
Figure 40 : The command to combine 2 tables to execute the function
- The result after executing the command Figure 41 : All necessary information to manage Book in the store
- Sales statistics for the certain day
❖ This function helps to report the total orders of the day, the total number of books sold and the
total revenue after each day.
- I would first use the COUNT function to calculate the total number of orders, then use the SUM
function to calculate the total number of books sold and the total sales of those orders. Next, I use
FROM and JOIN queries to get data from multiple linked tables. Finally, I will apply the WHERE
command to limit the amount of information that will only be retrieved in a certain day.
Figure 42 : The command to sales statistics of January 1, 2021
- The result of this function Figure 43 : All the information of the sale that day
