Download Database Assignment 1 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
Learner’s name Tran Quang Huy Assessor name
Learner’s ID GCD18457 Submission number 1
Learner declaration:
I certify that the work submitted for this assignment is my own and research sources are fully acknowledged.
Learner signature Huy Date 23/02/
Grading grid
P 1 M1 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
LO1 Use an appropriate design tool to design a relational database system for a substantial problem P1 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.
- An ERD clearly shows the complete logical design for the given scenario
- Write the normalization statement for each of the entity
M1 Produce a comprehensive design for a fully functional system which includes interface and output designs, data validations and data normalisation.
- Comprehensive design for a fully functional system
- Data validations and data normalisation
D1 Assess the effectiveness of the design in relation to user and system requirements.
Contents
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 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
PART1: TOOLINGS
1. SQL Server SQL (Structured Query Language) or structured query language Is a computer language type.
- Popular to create, edit and retrieve data from a relational database management system.
- The development of SQL goes far beyond the original purpose of servicing the object-relational database management systems.
- SQL is an ANSI / ISO standard Microsoft SQL Server is a relational database management system developed by Microsoft. As a database server, it is a software product with the primary function of storing and retrieving data as requested by other software applications—which may run either on the same computer or on another computer across a network (including the Internet). (Wikipedia, n.d.)
- SQL Server provides scripts for data inquiry tasks such as: o Insert, delete and update rows in 1 relationship o Journal, add, delete and modify objects in the database. o Control access to databases and objects of the database to ensure confidentiality, consistency and binding of databases. o Objects of SQL server are data tables with columns and rows. The column is called the data field and the row is the table record. The data column and the specified data type make up the structure of the table. When a table is organized into a system for a specific use of a job, it becomes a database.
PART2: DATABASE DESIGN
1. Database system overview
- The main database components of Electroshop: o Customers information o Salesperson information o Invoice order o Items information o Supplier information
- This relational Database system will be able to: o Create comparative reports. o Calculate: Profit of Electroshop. Salesperson’s bonus each month o Manage and control data.
2. Database system architecture: 2.1. Normalization Process: Base on ElectroShop’s requirements that make Database normalization is the process of structuring a relational database in accordance with a series of so-called normal forms in order to reduce data redundancy and improve data integrity.
- Upon consideration, the UNF table will necessary fields is as followed: UNF 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 Table 1 - UNF table
- The second normal form (2NF): After 1NF, all the entities have to be function-dependent on the keys. Base on 1NF that kind of item, number of items, items name, items price and supplier information are not dependent on the order details and belong to the item_id. So, it should make sense that items information be separated into a new set. The 2NF form will be: 2NF 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 item_name item_price supplier_name supplier_mail supplier_adress supplier_phone Table 3 - The second normal form (2NF):
- The third normal form (3NF): In this step, 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.
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 Table 5 - Fully Database system normal form
2.2. Entity Relationship Diagram (ER Diagram): Figure 2 - ElctroShop Entity Relationship Diagram
- 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. 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. Table 9 - Salespersons table Table 8 - Example Salespersons table
- Supplier table: This table is used to store information of ElectroShop’s supplier. This table includes: o Supplier’s Identity ( supplier_id - PK): This is primary key of supplier table to make sure that a supplier has a unique and non-duplicate ID. o Supplier’s name ( supplier_name ): Store supplier name information. o Supplier’s mail ( supplier_mail ): Store supplier mail information. o Supplier’s address ( supplier_address ): Store supplier address information. o Supplier’s phone number ( supplier_phone ): Store supplier phone information. Table 10 - Supplier table Table 11 - Example Supplier table
