Download Database Assignment 2 and more Assignments Database Management Systems (DBMS) in PDF only on Docsity!
ASSIGNMENT FRONT SHEET <No. 1 > Qualification BTEC Level 5 HND Diploma in Computing and Systems Development Unit number and title Unit 04 : Database Design & Development Assignment due Assignment submitted 2 Learner’s name Tran Quang Huy Assessor name Learner’s ID GCD18457 Submission number Learner declaration: I certify that the work submitted for this assignment is my own and research sources are fully acknowledged. Learner signature Huy Date Grading grid P2 P3 P4 P5 M2 M3 M4 M5 D2 D Assignment title Understand databases and data management systems
In this assignment, you will have opportunities to provide evidence against the following criteria. Indicate the page numbers where the evidence can be found. Assessment criteria Expected evidence Task no. Assessor’s Feedback LO2 Develop a fully functional relational database system, based on an existing system design. LO3 Test the system against user and system requirements. LO4 Produce technical and user documentation P2 Develop the database system with evidence of user interface, output and data validations, and querying across multiple tables.
- Code snippets to create each table & to insert some sample data for each table
- Generated Database Diagram
- Explanations about any changes comparing to your design
- Use the range of tools and techniques used to design (working or prototyping) user interfaces for these above system’s functionalities
M3 Assess whether meaningful data has been extracted through the use of query tools to produce appropriate management information. M4 Assess the effectiveness of the testing, including an explanation of the choice of test data used. M5 Produce technical and user documentation for a fully functional system, including ER Diagram and normalization statements and describing how the system works. D2 Evaluate the effectiveness of the database solution in relation to user and system requirements, and suggest improvements.
D3 Assess any future improvements that may be required to ensure the continued effectiveness of the database system.
Table of Contents
1 | P a g e
INTRODUCTION
Use an appropriate design tool to design a relational database system for substantial problem. Base on Assignment Brief that ElectroShop company sell electronic devices and they want to create a database to store system. This system will store the store’s necessary data, which are: Customer Information, Seller (Employee) Information, Product & Provider Information, Invoice Information Designed by using Relational Database and 3NF Normalization Process, this System will provide a fully functional Database which includes: Data validations, Data normalization Data interfaces for different situations, Data modification, Calculate Seller’s (Employee) bonus Requirement of ElectroShop’ application:
- The database will store the information and security data b. This system will store the store’s necessary data, which are: o Seller information o Customer information o Invoice o Product information o Supplier information
- Designed by using Relational Database and 3NF Normalization Process, this System will provide a fully functional Database which includes: o Data validations o Data normalization o Data modification o Calculate Seller’s (Employee) bonus
2 | P a g e
PART 1 : DATABASE DESIGN
1. Database system architecture
The Database system’s requirement additional steps should be taken so that no attribute can be transitively dependent on the primary key. That means they cannot be dependent on a non-primary key attribute in the same table. Upon inspection, a name of supplier is actually retrievable from supplier information and similarly to item information, customer information and salesperson information are dependent on item_id, customer_id, salesperson_id. One customer can buy many items and one item can be bought by many customers so the Database system need to have 1 table order_detail include item_id, order_id and order_quantity to solve that problem. In the beginning, ElectroShop has a small database system and is limited to paperwork by getting information from this Invoice: Figure 1 - Purchase invoice ElectroShop
4 | P a g e Conclusively, the Database system will have: UNF 1NF 2NF 3NF customer_name customer_mail customer_address customer_zipcode customer_phone salesperson_name salesperson_age salesperson_mail salesperson_phone salesperson_adress order_day order_quantity item_kind item_price supplier_name supplier_mail supplier_adress supplier_phone order_id item_kind item_price supplier_name supplier_mail supplier_adress supplier_phone item_id item_name item_price supplier_name supplier_mail supplier_adress supplier_phone order_id order_day customer_id salesperson_id order_id order_day order_quantity customer_name customer_mail customer_address customer_zipcode customer_phone salesperson_name salesperson_age salesperson_mail salesperson_phone salesperson_adress order_id item_id item_name item_price item_id order_id order_quantity order_id order_day order_quantity customer_name customer_mail customer_address customer_zipcode customer_phone salesperson_name salesperson_age salesperson_mail salesperson_phone salesperson_adress item_id item_kind item_price supplier_id supplier_name supplier_mail supplier_adress supplier_phone customer_id customer_name customer_mail customer_adress customer_zipcode customer_phone salesperson_id salesperson_name salesperson_age salesperson_mail salesperson_phone salesperson_adress login_id login_name login_passoword fk_customer_id Table 2 - Fully Database system normal form
5 | P a g e
2. Entity Relationship Diagram (ER Diagram):
Figure 2 - ElctroShop Entity Relationship Diagram
7 | P a g e 3.2. Salespersons table: This table is used to store information of ElectroShop’s employee information would help to determine which salesperson is in charge of selling which order. Many orders can be sold by one salesperson so it will have many relationships. CREATE TABLE salespersons( salesperson_id INT PRIMARY KEY, salesperson_name VARCHAR( 100 ), salesperson_phone VARCHAR( 100 ), salesperson_age INT, salesperson_mail VARCHAR( 100 ), ); Table 4 - Example Customer table Table 5 - Salespersons table
8 | P a g e This table includes: o Salesperson’s identity ( salesperson_id - PK): Each salesperson just only has one ID that make sure that salesperson has a unique and non-duplicate ID so this must be primary key. o Salesperson’s name ( salesperson_name ): Store salesperson name information. o Salesperson’s Age ( salesperson_age ): Store salesperson age information. o Salesperson’s phone number ( salesperson_phone ): Store salesperson phone number information. o Salesperson’s email ( salesperson_mail ): Store salesperson email information. 3.3. Supplier table This table is used to store information of ElectroShop’s supplier. Table 6 - Example Salespersons table CREATE TABLE supplier( supplier_id INT PRIMARY KEY, supplier_name VARCHAR( 100 ), supplier_mail VARCHAR( 100 ), supplier_adress VARCHAR( 100 ), supllier_phone VARCHAR( 100 ), );
10 | P a g e 3.4. Item table. This table is used to store information of ElectroShop’s orders. That also contains information about items that are being sold. The items are shown in the items table and in order details. This table includes: o Item identity ( item_id – PK): This is primary key of items table to make sure that item has a unique and non-duplicate ID. o Kind of Items ( item_kind ): Store kind of items information. o Price ( item_price ): Store price of items information. o Supplier Identity ( fk_supplier_id ): This is foreign key to link with supplier_id at supplier table. This allow people can know what specific items originated. CREATE TABLE items( item_id INT PRIMARY KEY, item_kind VARCHAR( 100 ), item_price INT, fk_supplier_id int, CONSTRAINT fk_supplier_id_items FOREIGN KEY (fk_supplier_id) REFERENCES supplier (supplier_id), ); Table 10 - Example Items table Table 9 - Items table
11 | P a g e 3.5. Order table This table is used to store information of ElectroShop’s orders. It is an important table to connect and reference by many others: A customer who order that items, a salesperson who sold that items, specially that connect with that Order detail table as know as invoice. CREATE TABLE orders( order_id INT PRIMARY KEY, order_day date, fk_customer_id int, CONSTRAINT fk_customer_id FOREIGN KEY (fk_customer_id) REFERENCES customers (customer_id), order_salesperson_id INT, CONSTRAINT fk_order_salesperson_id FOREIGN KEY (order_salesperson_id) REFERENCES salespersons (salesperson_id), ); Table 11 - Orders table
