ProductsLogo
LogoStudy Documents
LogoAI Grader
LogoAI Answer
LogoAI Code Checker
LogoPlagiarism Checker
LogoAI Paraphraser
LogoAI Quiz
LogoAI Detector
PricingBlogAbout Us
logo

Primary and Foreign Key | Exercise Solutions

Verified

Added on  2022/09/01

|8
|1070
|31
AI Summary

Contribute Materials

Your contribution can guide someone’s learning journey. Share your documents today.
Document Page
Solution 1
Exercise 1
Below are the elements –
1. Table Name :Company
Schema: Company (code,name,address,countryCode,telephoneNumber)
Constraint: Code is primary key
2. Table Name :Product
Schema: Product (productNumber,name,price,category,description,code)
Constraint:
productNumber is primary key
Code is foreign key
3. Table Name:Customer
Schema :Customer(customerID,name,address,phoneNumber)
Constraint :
customerID primary key
4. Table Name :Order
Schema :Order(OrderID,customerID,date,time)
Constraint :
OrderID is primary key
CustomerId is foreign key
5. Table Name :OrderDetails
Schema :OrderDetails (OrderID,productNumber)

Secure Best Marks with AI Grader

Need help grading? Try our AI Grader for instant feedback on your assignments.
Document Page
Constraint :
OrderID,productNumber is foreign key.
Exercise 2
Exercise 3
SQL statement to create table:
CREATE TABLE COMPANY (
CODE INT PRIMARY KEY
,NAME VARCHAR(100) NOT NULL
,ADDRESS VARCHAR(100) NOT NULL
,TELEPHONENUMBER VARCHAR(10)
);
CREATE TABLE PRODUT (
PRODUCTNUMBER INT PRIMARY KEY
,NAME VARCHAR(100) NOT NULL
,PRICE DECIMAL(8, 2)
Document Page
,CATEGORY VARCHAR(10)
,DESCRIPTION VARCHAR(100) NOT NULL
,CODE INT
,FOREIGN KEY (CODE) REFERENCES COMPANY(CODE)
);
CREATE TABLE CUSTOMER (
CUSTOMERID INT PRIMARY KEY
,NAME VARCHAR(100) NOT NULL
,ADDRESS VARCHAR(100) NOT NULL
,PHONENUMBER VARCHAR(10)
);
CREATE TABLE `ORDER` (
ORDERID INT PRIMARY KEY
,CUSTOMERID INT
,DATE DATE
,TIME TIME
,FOREIGN KEY (CUSTOMERID) REFERENCES CUSTOMER(CUSTOMERID)
);
CREATE TABLE ORDERDETAILS (
ORDERID INT
,PRODUCTNUMBER INT
,PRIMARY KEY (
ORDERID
,PRODUCTNUMBER
)
,FOREIGN KEY (ORDERID) REFERENCES `ORDER`(ORDERID)
,FOREIGN KEY (PRODUCTNUMBER) REFERENCES PRODUCT(PRODUCTNUMBER)
);
Solution 2
Exercise 1
CREATE TABLE Customer (
ID INT
,Customer_FName VARCHAR(255)
,Customer_LName VARCHAR(255)
);
INSERT INTO Customer (
ID
,Customer_FName
,Customer_LName
)
VALUES (
'10001'
,'John'
,'Smith'
);
CREATE TABLE Buying (
Customer_ID INT
,Product_ID INT
Document Page
,Order_Time DATE
);
INSERT INTO Customer (
Customer_ID
,Product_ID
,Order_Time
)
VALUES (
'10001'
,'772'
,'2016/09/01'
);
CREATE TABLE Product (
ID INT
,Name VARCHAR(255)
,Address VARCHAR(255)
);
INSERT INTO Customer (
ID
,Name
,Address
)
VALUES (
'772'
,'Telephone'
,'22 Ave, Burwood'
);
Exercise 2
SELECT *
FROM Customer
INNER JOIN Buying ON Buying.Customer_ID = Customer.ID
WHERE extract(year FROM DATE Buying.Order_Time) <= 2017;
Exercise 3
SELECT *
FROM Customer
INNER JOIN Buying ON Buying.Customer_ID = Customer.ID
WHERE Customer.Customer_FName LIKE '%o%'
OR Customer.Customer_LName LIKE '%o%'
Exercise 4
SELECT *
FROM Customer
INNER JOIN Buying ON Buying.Customer_ID = Customer.ID
INNER JOIN Product ON Buying.Product_ID = Product.ID
WHERE Product.Address LIKE '%Burwood%'
Exercise 5
SELECT *
FROM Customer

Paraphrase This Document

Need a fresh take? Get an instant paraphrase of this document with our AI Paraphraser
Document Page
INNER JOIN Buying ON Buying.Customer_ID = Customer.ID
INNER JOIN Product ON Buying.Product_ID = Product.ID
WHERE Name = 'Telephone'
OR Product.Address LIKE '%Geelong%';
Solution 3
Exercise 1
Transitive dependencies are as follows –
{ord_no}  {Name}
So, we will split the tables having customer details and order details
Exercise 2
Partial dependencies are –
Company depends only on Prod_ID
Description depends only on Prod_ID
Order date depends only on order_no
Exercise 3
Relational db is as follows –
CUST_I
D NAME
C001 GOLD
C009 BLUE
C075 RED
ORD_N
O
ORD_DAT
E
CUST_I
D
81 15-Apr C001
99 16-Apr C075
56 16-Apr C009
88 17-Apr C001
PRD_ID DESC COMPANY ORD_N PROD_I QUANTIT
Document Page
O D Y
P005 CHISEL A 81 P005 6
P004 PLANE A 81 P004 14
P015 SAW B 99 P015 3
P033 PUNCH C 56 P033 24
56 P004 9
88 P015 10
88 P005 2
Exercise 4
Solution 4
Exercise 1
Without any Normalization
customer [ customer_no, customer_name, customer_address, customer_phone, ( artist_id,
artist_name,artist_title, purchase_date, price) ]
First Normal Form(1NF)
Document Page
customer [ customer_no, customer_name, customer_address, customer_phone]
customer_art [ customer_no, art_code, purchase_date, artist_id, artist_name, artist_title, price ]
Please note that the key selected for the repeating group is the piece of art itself than once, the
purchase date was added as part of the key to make the rows unique.
Second Normal Form(2NF)
customer [ customer_no, customer_name, customer_address, customer_phone]
customer_art [ customer_no, art_code, purchase_date, price ]
art [ art_code, artist_title, artist_id, artist_name ]
Third Normal Form(3NF)
customer [ customer_no, customer_name, customer_street, customer_city, customer_prov,
cust_postalcode, customer_phone]
customer_art [ customer_no, art_code, purchase_date, price ]
art [ art_code, artist_title, artist_id(FK) ]
artist [ artist_id, artist_fname, artist_lname ]
Exercise 2
CREATE TABLE customer (
[customer_no] INT NOT NULL identity
,[customer_name] NVARCHAR(255)
,[customer_street] NVARCHAR(255)
,[customer_city] NVARCHAR(255)
,[customer_prov] NVARCHAR(255)
,[cust_postalcode] NUMERIC(9, 2)
,[customer_phone] NUMERIC(9, 2)
,PRIMARY KEY ([customer_no])
);
CREATE TABLE customer_art (
[customer_no] INT NOT NULL identity
,[art_code] NUMERIC(9, 2)
,[purchase_date] DATE
,[price] NUMERIC(9, 2)
,PRIMARY KEY ([customer_no])
);

Secure Best Marks with AI Grader

Need help grading? Try our AI Grader for instant feedback on your assignments.
Document Page
CREATE TABLE art (
[art_code] INT NOT NULL identity
,[artist_title] NVARCHAR(255)
,[artist_id] NUMERIC(9, 2)
,PRIMARY KEY ([art_code])
);
CREATE TABLE artist (
[artist_id] INT NOT NULL identity
,[artist_fname] NVARCHAR(255)
,[artist_lname] NVARCHAR(255)
,PRIMARY KEY ([artist_id])
);
1 out of 8
[object Object]

Your All-in-One AI-Powered Toolkit for Academic Success.

Available 24*7 on WhatsApp / Email

[object Object]