Department of Computing - Database Design and SQL Queries

This assignment solution from the Department of Computing's Business Information Systems program demonstrates a comprehensive understanding of database design principles. It begins by identifying entities, primary keys, and foreign keys within a business context. The solution then presents logical Entity Relationship Diagrams (ERDs) to visually represent the database structure. Crucially, the document includes detailed SQL code, with CREATE TABLE and INSERT INTO statements for various entities such as Products, Sets, Customer, Orders, Payment, and Salesman. The solution also incorporates the creation of tables for relationships between entities (order_sets, salesman_sets, and products_sets). Finally, the assignment concludes with example SQL queries, including aggregation and selection queries, to retrieve and manipulate data from the database, showcasing practical application of database concepts.

Department of Computing
Computer Science Practice
Date of Submission: 10/08/2018
Degree Program: Business Information Systems

1 | P a g e
TABLE OF CONTENT
Page
1. Identification of Entities and Primary and Foreign Keys 2
2. Entity Relationship Diagrams 3
3. Codes 4
• Products
• Sets
• Customer
• Orders
• Payment
• Salesman
• order_sets
• salesman_sets
• products_sets
4. Queries 13

2 | P a g e
Identification of Entities and Primary Keys and Foreign Keys
Entity Attributes Primary Key Foreign Key Rationale for PK
Products productName
productId
productDescription
productDescription
productId productId can
be used to
identify the
product
Sets setName
setId
setNoOfProducts
setType
setId setId is a unique
identifier for
products.
Customer customerName
customerId
customerAddress
customerTelephone
customerId customerId can
be used to
identify the
customer
orders orderDate
orderId
orderDescription
customerId
orderId customerId orderId is used
to identify
orders.
payment paymentId
paymentMethod
paymentDate
paymentAmount
paymentId PaymentId is
used to identify
payments of
different
customers
uniquely
salesman salesmanName
salesmanId
salesmanTelephone
salesmanCommision
salesmanSalary
salesmanId salesmanId can
be used to
identify each
salesman.
order_sets idOrdering
idSet
(idOrdering,idSet) orderId
setId
idOrdering is
used to identify
the order.
setId is used to
identify the
products.
salesman_sets idsalesman
idSet
(idsalesman,idSet) salesmanId
setId
idSalesman is
used to identify
the salesman.
idSet isuse to
identify the the

3 | P a g e
products.
products_sets idProducts (idProducts ,idSet) productsId idProduct is
idSet setId used to identify
the product.
setId is used to
identify the
products.
Entity Relationship Diagrams
Logical ER Diagram

4 | P a g e
Codes
Products
CREATE TABLE Products(
productName varchar(10),
productId int(5) PRIMARY KEY,
productDescription varchar(20),
productDescription double
);
INSERT INTO products VALUES('Skin care sets',4508,'Skin Use Only',1200);
INSERT INTO products VALUES('Make-up sets',4506,'Make Up Set',1600);
INSERT INTO products VALUES('Fragrance sets',4510,'Body Use Only',800);
INSERT INTO products VALUES('Hair care setss',4500,'Hair Use Only',1000);

5 | P a g e
Sets
CREATE TABLE Sets(
setName varchar(10),
setId int(5) PRIMARY KEY,
setNoOfProducts int(5),
setType varchar(18)
);
INSERT INTO sets VALUES('CH911',911,215,'Health Care');
INSERT INTO sets VALUES('FS930',930,715,'fragrance sets');
INSERT INTO sets VALUES('SS211',211,120,'Skin care sets');
INSERT INTO sets VALUES('MU101',101,350,'make-up sets');

6 | P a g e
Customer
CREATE TABLE Customer(
customerName varchar(10),
customerId int(5) PRIMARY KEY,
customerAddress varchar(25),
customerTelephone int(10)
);
INSERT INTO customer VALUES('John',001,'Lancaster',0773142598);
INSERT INTO customer VALUES('Ann',002,'cantwebury',0773114354);
INSERT INTO customer VALUES('Steeve',003,'scotland',0773142597);
INSERT INTO customer VALUES('Richard',004,'lord provosts',0774142598);
INSERT INTO customer VALUES('John',005,'lord mayors',0773152598);

7 | P a g e
Orders
CREATE TABLE orders(
orderDate varchar(10),
orderId int(5),
orderDescription varchar(25),
customerId int(5),
CONSTRAINT pk_order PRIMARY KEY(orderId),
CONSTRAINT fk_order FOREIGN KEY(customerName) REFERENCES
customer(customerId)
);
INSERT INTO ordering VALUES(21062018,100801,'Delivered',004);
INSERT INTO ordering VALUES(18072017,100832,'Not-Delivered',002);
INSERT INTO ordering VALUES(12092018,100861,'Delivered',003);
INSERT INTO ordering VALUES(09052018,100893,'Not-Delivered',005);
INSERT INTO ordering VALUES(01062018,100850,'Delivered',001);

8 | P a g e
CREATE TABLE payment(
Payment
paymentId int(5) PRIMARY KEY,
paymentMethod varchar(10),
paymentDate date,
paymentAmount double
);
INSERT INTO payment VALUES(100801,'Paypal',21062018,4800);
INSERT INTO payment VALUES(100832,'Paypal',18072017,5200);
INSERT INTO payment VALUES(100861,'Paypal',12092018,4200);
INSERT INTO payment VALUES(100893,'Paypal',09052018,1800);
INSERT INTO payment VALUES(100850,'Paypal',01062018,2500);

9 | P a g e
CREATE TABLE salesman(
Salesman
salesmanName varchar(10),
salesmanId int(5) PRIMARY KEY,
salesmanTelephone int(10),
salesmanCommision int(6),
salesmanSalary int(6)
);
INSERT INTO salesman VALUES('Christopher',101,0766844584,12000,48000);
INSERT INTO salesman VALUES('Jack',102,0772552988,16000,56000);
INSERT INTO salesman VALUES('Drew',103,0721731998,17500,42000);
INSERT INTO salesman VALUES('Mike',104,0774180598,11500,45000);
INSERT INTO salesman VALUES('Shana',105,0773155098,10000,50000);

10 | P a g e
Order Sets
CREATE TABLE order_sets(
idOrdering int(5),
idSet int(5),
PRIMARY KEY(idOrdering,idSet),
FOREIGN KEY(idOrdering) REFERENCES ordering(orderId),
FOREIGN KEY(idSet) REFERENCES sets(setId)
);
INSERT INTO order_sets VALUES(100801,911);
INSERT INTO order_sets VALUES(100832,930);
INSERT INTO order_sets VALUES(100861,211);
INSERT INTO order_sets VALUES(100893,101);
INSERT INTO order_sets VALUES(100850,911);

11 | P a g e
Salesman Sets
CREATE TABLE salesman_sets(
idsalesman int(5),
idSet int(5),
PRIMARY KEY(idsalesman,idSet),
FOREIGN KEY(idsalesman) REFERENCES salesman(salesmanId),
FOREIGN KEY(idSet) REFERENCES sets(setId)
);
INSERT INTO salesman_sets VALUES(101,911);
INSERT INTO salesman_sets VALUES(102,930);
INSERT INTO salesman_sets VALUES(103,211);
INSERT INTO salesman_sets VALUES(104,101);
INSERT INTO salesman_sets VALUES(105,911);

12 | P a g e
Products Sets
CREATE TABLE products_sets(
idProducts int(5),
idSet int(5),
PRIMARY KEY(idProducts ,idSet),
FOREIGN KEY(idProducts) REFERENCES products(productsId),
FOREIGN KEY(idSet) REFERENCES sets(setId)
);
INSERT INTO products_sets VALUES(4508,911);
INSERT INTO products_sets VALUES(4506,930);
INSERT INTO products_sets VALUES(4510,211);
INSERT INTO products_sets VALUES(4500,101);
INSERT INTO products_sets VALUES(4506,911);

13 | P a g e
Queries
SELECT SUM(paymentAmount)*2/100 FROM payment
SELECT DISTINCT orderid, orderDate
FROM ordering;
SELECT MAX(salesmanSalary) FROM salesman

14 | P a g e
SELECT MIN(salesmanSalary) FROM salesman