Download Assignment 1 Database Management Systems and more Assignments Database Management Systems (DBMS) 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 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
P1 M1 D
Summative Feedback: Resubmission Feedback:
Grade: Assessor Signature: Date: Signature & Date:
Table of Contents
- CHAPTER 1 – Statements of user and system requirements
- I. Overview about the problem
- II. Requirements of the application
- CHAPTER 2 – Design the relational database system
- I. Analyze the requirements..................................................................................................................................................................................
- Entity Relationship Diagram (ERD) of the system
- II. Database design with explanations
- III. Review whether the database is normalized
- Figure 1: Entity Relationship Diagram of Book Store Table of Figures
- Figure 2: Customer Table
- Figure 3: Example of Customer Table
- Figure 4: Book Table.......................................................................................................................................................................................................
- Figure 5: Example of Book Table
- Figure 6: Inventory Table
- Figure 7: Example of Inventory Table
- Figure 8: OrderDetail Table
- Figure 9: Example of OrderDetail Table
- Figure 10: Orders Table
- Figure 11: Example of Orders Table
- Figure 12: Database System Diagram
- Figure 13: Example 1NF and 2NF normalized response table
- Figure 14: Example 3NF normalized response table
CHAPTER 1 – Statements of user and system requirements I. Overview about the problem Currently, FPT Greenwich University (Viet Nam) is facing difficulties in dealing with managing the library of the university. Libraries are often a place where students can find reference books for their major or assignments, and also a place where they can self – study at school. However, sometimes the books they are looking for are not available at the library that can make students uncomfortable in certain circumstances, the solution will be open online bookstore for students of the university. The bookstore will be linked with publishers, if students want to buy books, the publishers will be sent them to the university for students to pick up or can deliver them to their homes. Therefore, it is necessary to have a system to manage the bookstore such as order management, inventory management, etc. II. Requirements of the application ❖ Manager
- They can manage orders of customer to calculate revenue
- They can manage the information of books to show for customers in system
- They can manage the number of books in the publisher’s inventory to update the system regularly
- They can manage the information of customers and their order
- They can create new order for each customer ❖ Customers
- They can find the book they want and can the see the price and information of this book
- They can order book by system
- The customers can set their home, so that the books can be delivered to their home after ordered. ❖ 5 Advanced Statistics Actions
- Statistics of 3 products with the most orders to import from publisher
- Statistics of 3 products with the least orders to recalculate the number of imports of that product each month
- Statistics of customers with the highest orders in 1 month to give them a coupon for next order
- Statistics days with has the highest revenue to strengthen the shopkeeper to be able to receive orders and order shippers to pick up goods to deliver to customers
- Statistics days with has the lowest revenue to increase advertising on social networking platforms to attract new customers
❖ Table Descriptions: The database system has 4 tables, which are: ❖ Table 1: Customer
- This table is used to store customer’s information like Name, Phone number, Address. This information is necessary in compiling a list of customers or used when customers have a need for home delivery.
- Customer identity (ID - PK): This is the primary key of the customer table to ensure that the customer has unique and non- duplicated ID with others customers.
- Customer Name (Full Name): Store name of customer to classify with others customers.
- Customer Phone number: Easily contact customers when needed
- Customer Address: To delivery book to customer’s home Figure 2 : Customer Table Figure 3 : Example of Customer Table
❖ Table 2: Book
- This table is used to store information about book. This table will help manager update information of book and customers can see the information of book, so that they can pick what they want.
- Book Identity (ID - PK): Each book has only one unique ID, make sure book has a unique and not duplicate ID, so this should be the primary key.
- Book Name: Store name or title of books
- Book Publisher: Store name of publishers
- Book Date Publish: Store the date that book have been published
- Book Price: Store price for each book Figure 4 : Book Table
Figure 7 : Example of Inventory Table ❖ Table 4: OrderDetail
- This table is used to store information about the order of books. It is an important board for many others to connect and refer to: A customer has ordered those products and shipper will contact with customer and deliver them to their home. This order sheet includes:
- Order Identifier (ID - PK): This is primary key to confirm that when a customer places an order, the identity of this order exists only and cannot duplicated.
- Order Customer (FK): This is foreign key reference to the customer’s table. It will allow people to know information about the customer who is placing an order.
- Order book (FK): This is foreign key reference to the book’s table to show what books the customer ordered
- Number of Book: The number of books they ordered.
- Order date: Store information about the date the customer placed an order.
- Total Price: The total price of order
Figure 8 : OrderDetail Table Figure 9 : Example of OrderDetail Table ❖ Table 5 : Orders
- This table is used to aggregate customer requirements into one order to facilitate customer payment and store statistics This order sheet includes:
- Order Identifier (ID - PK): This is primary key to confirm that when a customer places an order, the identity of this order exists only and cannot duplicated.
- Order Customer (FK): This is foreign key reference to the customer’s table. It will allow people to know information about the customer who is placing an order.
- Total Price: The total price of order
Figure 12 : Database System Diagram ❖ The figure above is a diagram of this system, it shows the relationship between the tables. First, a bookstore will definitely have a ‘Book’ table and the properties in the table that represent the information of the that book. Since this is an online bookstore, it will be necessary to have an ‘Inventory’ table to store the book after receiving it from the publisher to store and save information such as the warehouse address so that when you need to pick up the book, deliver the book to the shipper know to come get. Next, we need the ‘Customer’ table to store customer information such as Name, PhoneNumber and Address used when the customer places an order, the manager will call at the saved phone number to confirm the order goods and delivered to the customer’s home address. Finally, for any store there will be a need for an ‘OrderDetail’ table to manage the orders and the information in that order such as Customer Name, Books Purchased, Number of Books Purchased, Book Price, Date Buy for easy access to statistics.
❖ Database analysis ❖ Customer management
- ID-> int - > primary key - > unique
- FullName - > nvarchar(50)
- PhoneNumber - > nvarchar(10)
- Address - > nvarchar(50) ❖ Book management
- ID - > int - > primary key - > unique
- BookName - > nvarchar(50)
- BookPublisher - > nvarchar(50)
- BookDatePublisher - > date
- BookPrice - > float ❖ Inventory management
- ID - > int - > primary key - > unique
- Numberofbook - > int
- InventoryAddress - > nvarchar(50) ❖ Order management
- ID - > int - > primary key - > unique
- OrderCustomer - > int
- OrderBook - > int
- NumberofBook - > int
- OrderDate - > date
- TotalPrice - > float III. Review whether the database is normalized
- It is important to review whether the database is normalized. If it is not normalized, your database will lose its efficiency. This article examines the different types of normalization required to create a working database. There are several reasons we want to do this process: ❖ Make the database more efficient ❖ Prevent the same data from being stored in more than one place (known as "unusual insertion") ❖ Prevents updates from being made to some data but not others (known as "update anomalies")
Figure 14 : Example 3NF normalized response table o In this table, the primary key of the table is BookID and other columns is attributes of BookID. Because others columns are non- key attributes, manager can change the information of these columns without affecting the primary key, and other tables associated with the primary key will not be affected.
