The local hardware store has always kept paper copies of the invoices that they
ID: 3787121 • Letter: T
Question
The local hardware store has always kept paper copies of the invoices that they generate for customers or they sometimes enter it in a spreadsheet. They noted some redundancies and repeating sets of data and so they were advised to store their data in database instead of a spreadsheet. Now, they would like you to create a database for keeping their records. Details that they wish to store include: Order Number, Order Date, Customer Id , Customer Name, Customer Address , Cashier Id, Cashier Name, Item Number , Item Description, Quantity Ordered, Unit Price. Data for each customer, cashier, item and order are all saved for future use. Do each of the following steps and show how you progress through the Normal Forms: Normalize the database to the First Normal Form (10 POINTS) Normalize the database to the Second Normal Form (10 POINTS) Normalize the database to the Third Normal Form (10 POINTS) Identify the primary keys and foreign keys in each table, wherever applicable (10 POINTS)
Explanation / Answer
CREATE DATABASE my_db
** database tables can be added with CREATE TABLE statement.
Creating a basic table involves naming thr table and defining its columns and each column's data type.
Basic syntax to cerate the table are as follows.
CREATE TABLE table_name(
cloumn1 datatype,
column2 datatype,
column3 datatype
...
column datatype
PRIMARY KEY (one or more columns)
);
Create table is the key word telling the database system excatly what we are going to create.The table name should be unique.
CREATE TABLE HARDWARE (
Order Number INT NOT NULL,
Order Date date(),
Customer Id INT NOT NULL,
Customer Name Varchar (30),
Customer Address Char(30) ,
Cashier Id int(11),
Cashier Name VARCHAR(20),
Item Number INT(30) ,
Item Description VARCHAR (60),
Quantity Ordered INT (100),
Unit Price DECIMAL (18,2).
PRIMARY KEY (Customer ID)
);
Noe the table hardware is available in your database which you can also update further information using the UPDATE commane and use DELETE command to delete the rows in the table.
As per the 1 NF , we need to ensure that that there are no repeating groups of data. So let us break and using the keys.
CREATE TABLE ORDERS(
Order Number INT NOT NULL,
Order Date date(),
Customer Name Varchar (30),
Item Number INT(30) ,
Item Description VARCHAR (60),
Quantity Ordered INT (100),
Unit Price DECIMAL (18,2).
PRIMARY KEY (ORDER ID)
In case of the second normal form there must not be any partial dependency of any column on primary key. It means that for a table that has concentrated primary key, each column in the table that is not part of the primary key must depended upon the entire concatenated key for its existence. If any column depends only on one part of the cancatenated key, then the table 2NF.
);
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.