ISY1002/ISY103 Database Management for Business Assignment Help
Part A:

Part B:
above relation is in the 3NF as there are no transitive dependency between the tables. All the tables satisfy the conditions to be in the 3NF hence we can say that given scenario is in the 3NF. Every table has the primary key which makes the record unique and remove data redundancy and there are no multivalued attributes and all the non-key attributes depend on the primary key.
ORDER (o_id, Date, p_id, quantity)
STORE (s_id, s_ame)
SUPPLIER (sup_id, Sup_Name, Adress, Ph_no, email)
CUSTOMER (c_id, c_fn,c_ln, ph_no, Adress)
EMPLOYEE (e_id, e_fn,e_ln, address,ph_no, email, tfn, salary, JoiningDate, s_id,d_type)
PAYSLIP (pay_id, NoOfHrs, GrossPay, e_id)
DEPARTMENT (d_id, d_name, Ph_no, email)
PRODUCT (p_id, p_name, Brand, Description, price, o_id)
Part C:
customer table:
Field |
Type |
constraint |
c_id |
int |
PK |
c_fn |
text |
NOT NULL |
c_ln |
text |
NOT NULL |
adress |
varchar(20) |
NOT NULL |
ph_no |
bigint |
NOT NULL |
supplier table
Field |
Type |
Constraint |
sup_id |
int |
PK |
sup_name |
text |
NOT NULL |
adress |
text |
NOT NULL |
|
varchar(20) |
NOT NULL |
phone |
varchar(20) |
NOT NULL |
product table
Field |
Type |
Constraint |
p_id |
int |
PK |
p_name |
text |
NOT NULL |
description |
text |
NOT NULL |
brand |
text |
NOT NULL |
price |
int |
NOT NULL |
sup_id |
int |
FK |
store table:
Field |
Type |
Constraint |
s_id |
int |
PK |
s_name |
text |
NOT NULL |
adress |
varchar(20) |
NOT NULL |
|
varchar(20) |
NOT NULL |
quantity |
int |
NOT NULL |
fax_no |
varchar(20) |
NOT NULL |
ph_no |
varchar(20) |
NOT NULL |
p_id |
int |
FK |
orderr table
Field |
Type |
Constraint |
o_id |
int |
PK |
o_name |
text |
NOT NULL |
quantity |
int |
NOT NULL |
date |
date |
NOT NULL |
p_id |
int |
FK |
c_id |
int |
FK |
department table
Field |
Type |
Constraint |
d_id |
int |
PK |
d_name |
text |
NOT NULL |
|
varchar(20) |
NOT NULL |
ph_no |
varchar(20) |
NOT NULL |
employee table:
Field |
Type |
Constraint |
e_id |
int |
PK |
e_fn |
text |
NOT NULL |
e_ln |
text |
NOT NULL |
|
varchar(20) |
NOT NULL |
ph_no |
int |
NOT NULL |
d_type_id |
int |
FK |
s_id |
int |
FK |
joining_date |
date |
NOT NULL |
address |
varchar(20) |
NOT NULL |
tfn |
int |
NOT NULL |
salary |
int |
NOT NULL |
payslip table
Field |
Type |
Constraint |
pay_id |
int |
PK |
e_id |
int |
FK |
s_id |
int |
FK |
hours_worked |
int |
NOT NULL |
grosspay |
int |
NOT NULL |
ph_no |
int |
NOT NULL |
postgres=#
create database amcdb;
CREATE DATABASE
postgres=# \c amcdb;
You are now connected to database "amcdb" as user "postgres".
amcdb=# CREATE TABLE customer (
amcdb(# c_id int NOT NULL,
amcdb(# c_fn text NOT NULL,
amcdb(# c_ln text NOT NULL,
amcdb(# adress varchar(25) NOT NULL,
amcdb(# ph_no bigint NOT NULL,
amcdb(# PRIMARY KEY (c_id));
CREATE TABLE
amcdb=# CREATE TABLE supplier (
amcdb(# sup_id int NOT NULL,
amcdb(# sup_name text NOT NULL,
amcdb(# adress varchar(20) NOT NULL,
amcdb(# email varchar(20) NOT NULL,
amcdb(# phone varchar(20) NOT NULL,
amcdb(# PRIMARY KEY (sup_id));
CREATE TABLE
amcdb=# CREATE TABLE product (
amcdb(# p_id int NOT NULL,
amcdb(# p_name text NOT NULL,
amcdb(# description text NOT NULL,
amcdb(# brand text NOT NULL,
amcdb(# price int NOT NULL,
amcdb(# sup_id int NOT NULL,
amcdb(# PRIMARY KEY (p_id),
amcdb(# CONSTRAINT tg FOREIGN KEY (sup_id) REFERENCES supplier (sup_id) MATCH SIMPLE ON UPDATE NO ACTION ON DELETE NO ACTION);
CREATE TABLE
amcdb=# CREATE TABLE store (
amcdb(# s_id int NOT NULL,
amcdb(# s_name text NOT NULL,
amcdb(# adress varchar(20) NOT NULL,
amcdb(# email varchar(20) NOT NULL,
amcdb(# quantity int NOT NULL,
amcdb(# fax_number varchar(20) NOT NULL,
amcdb(# ph_no varchar(20) NOT NULL,
amcdb(# p_id int NOT NULL,
amcdb(# PRIMARY KEY (s_id),
amcdb(# CONSTRAINT f FOREIGN KEY (p_id) REFERENCES product (p_id));
CREATE TABLE
amcdb=# CREATE TABLE orderr (
amcdb(# o_id int NOT NULL,
amcdb(# o_name text NOT NULL,
amcdb(# quantity int NOT NULL,
amcdb(# date date NOT NULL,
amcdb(# p_id int NOT NULL,
amcdb(# c_id int NOT NULL,
amcdb(# PRIMARY KEY (o_id),
amcdb(# CONSTRAINT pk FOREIGN KEY (c_id) REFERENCES customer (c_id),
amcdb(# CONSTRAINT jk FOREIGN KEY (p_id) REFERENCES product (p_id));
CREATE TABLE
amcdb=# CREATE TABLE department (
amcdb(# d_id int NOT NULL,
amcdb(# d_name text NOT NULL,
amcdb(# ph_no varchar(20) NOT NULL,
amcdb(# email varchar(20) NOT NULL,
amcdb(# PRIMARY KEY (d_id));
CREATE TABLE
amcdb=# CREATE TABLE employee (
e_id int NOT NULL,
e_fn text NOT NULL,
e_ln text NOT NULL,
email varchar(15) NOT NULL,
ph_no int NOT NULL,
d_type_id int NOT NULL,
s_id int NOT NULL,joining_date date NOT NULL,
address varchar(50) NOT NULL,
tfn int NOT NULL,
salary int NOT NULL,
PRIMARY KEY (e_id),
CONSTRAINT sid FOREIGN KEY (s_id) REFERENCES store (s_id), CONSTRAINT type FOREIGN KEY (d_type_id) REFERENCES department (d_id) ON DELETE NO ACTION ON UPDATE NO ACTION);
CREATE TABLE
amcdb=# CREATE TABLE payslip(
amcdb(# pay_id int NOT NULL,
amcdb(# e_id int NOT NULL,
amcdb(# s_id int NOT NULL,
amcdb(# hours_worked int NOT NULL,
amcdb(# gross_pay int NOT NULL,
amcdb(# ph_no varchar(20) NOT NULL,
amcdb(# PRIMARY KEY (pay_id),
amcdb(# CONSTRAINT eid FOREIGN KEY (e_id) REFERENCES employee (e_id));
CREATE TABLE
amcdb=# \dt
List of relations
Schema | Name | Type | Owner
--------+------------+-------+----------
public | customer | table | postgres
public | department | table | postgres
public | employee | table | postgres
public | orderr | table | postgres
public | payslip | table | postgres
public | product | table | postgres
public | store | table | postgres
public | supplier | table | postgres
INSERT INTO `customer` VALUES (1412,'bella','fregusan','109/9b-skovde,sweden,2569',7784459635),(1432,'omar','khan','25 syria, 123654',9984541235),(1452,'lucija','marinkovic','25 zagreb,crotia 123654',7984561235),(1632,'abdul','ahmed','89 sudan 2569',9984451235),(1932,'bella','fregusan','89 spain,25693',7784451235);
INSERT INTO `department` VALUES (1,'HR',789456123,'abcd@gmail.com'),(2,'accounts',289456123,'abcde@gmail.com'),(3,'finance',689456123,'abcdef@gmail.com'),(4,'sales',696456123,'abcdefg@gmail.com'),(5,'security',996456123,'abcdefgh@gmail.com');
INSERT INTO `employee` VALUES (13,'shane','watson','shwa@gmail.com',2147483647,5,11,'2016-08-01','perth,australia',265,17000),(25,'martha','max','mmax@gmail.com',1234567881,3,12,'2017-09-01','syndney,australia',26,2000),(103,'shankar','khosla','shank@gmail.com',797359559,2,13,'2019-08-12','toronto,canada ',22,300000),(109,'victoria','ashley','va@gmail.com',258961475,5,14,'2017-10-04','manitoba,canada ',66,5000),(125,'glenn','maxwell','gmax@gmail.com',1234567891,1,12,'2017-08-01','brisbane,australia',26,7000);
INSERT INTO `orderr` VALUES (1004,'white walkers',15000,'2019-01-11',1,1412),(1005,'fishes',5600,'2018-10-11',3,1432),(1006,'dragons',200,'2019-10-11',2,1632),(1008,'phones',150,'2017-08-25',5,1432),(1011,'water',0,'2018-04-10',4,1932);
INSERT INTO `payslip` VALUES (1,125,11,60,5000,789456123),(2,25,12,12,600,132345678),(3,13,13,60,2000,741258963),(4,103,14,15,12300,797359559),(5,109,15,88,400,321654987);
INSERT INTO `product` VALUES (1,'fashion','new trends for new people','BOSS',1500,1),(2,'health','best for body growth','endura mass',200,2),(3,'beauty','give your lips a new look','MAC',1200,3),(4,'elctronics','full featured phone','oneplus7t',2000,1),(5,'bike care','polish and shine','f1- formula ',50,3);
INSERT INTO `store` VALUES (11,'abc','abc,123,australia','abcd@gmail.com',500,123456,987654321,1),(12,'abcd','abcd,1234,georGe','abc@gmail.com',500,123556,987654320,2),(13,'abcde','abcde,1234,george','abce@gmail.com',1500,113556,987654300,2),(14,'bcde','bcde,234,George','bce@gmail.com',100,313556,987654380,5),(15,'cde','cde,34,GEORGE','ce@gmail.com',103,313596,987684380,2);
INSERT INTO `supplier` VALUES (1,'ashish','abc,123,australia','bce@gmail.com',797359559),(2,'ash','abhc,123,audstralia','bces@gmail.com',797359589),(3,'zins','zins,123,audstralia','zies@gmail.com',997359589);
Part D:
1.amcdb=# SELECT CONCAT(c_fn,’ ‘, c_ln) AS FULL_NAME, adress, ph_no FROM customer
ORDER BY c_id DESC;
+-------------------+---------------------------+------------+
| FULL_NAME | adress | ph_no |
+-------------------+---------------------------+------------+
| bella fregusan | 89 night,spain,25693 | 7784451235 |
| abdul ahmed | 89 sudan ,south arab,2569 | 9984451235 |
| lucija marinkovic | 25 zagreb,crotia 123654 | 7984561235 |
| omar khan | 25 syria, 123654 | 9984541235 |
| bella fregusan | 109/9b-skovde,sweden,2569 | 7784459635 |
+-------------------+---------------------------+------------+
2.amcdb=# amcdb=# SELECT * FROM product WHERE price <= 100;
+------+-----------+------------------+--------------+-------+
| p_id | p_name | description | brand | price |
+------+-----------+------------------+--------------+-------+
| 5 | bike care | polish and shine | f1- formula | 50 |
+------+-----------+------------------+--------------+-------+
3.amcdb=# SELECT p.p_id , p.p_name , o.quantity , p.price , o.quantity * p.price AS total FROM orderr AS o INNER
JOIN product AS p ON p.p_id=o.p_id WHERE o.o_id=1005;
+------+--------+----------+-------+---------+
| p_id | p_name | quantity | price | total |
+------+--------+----------+-------+---------+
| 3 | beauty | 5600 | 1200 | 6720000 |
+------+--------+----------+-------+---------+
4.amcdb=# SELECT * FROM store WHERE adress LIKE ‘%george%’;
+------+--------+-------------------+----------------+----------+------------+-----------+
| s_id | s_name | adress | email | quantity | fax number | ph_no |
+------+--------+-------------------+----------------+----------+------------+-----------+
| 12 | abcd | abcd,1234,georGe | abc@gmail.com | 500 | 123556 | 987654320 |
| 13 | abcde | abcde,1234,george | abce@gmail.com | 1500 | 113556 | 987654300 |
| 14 | bcde | bcde,234,George | bce@gmail.com | 100 | 313556 | 987654380 |
| 15 | cde | cde,34,GEORGE | ce@gmail.com | 103 | 313596 | 987684380 |
+------+--------+-------------------+----------------+----------+------------+-----------+
- does not favour 3nf . As the table is in 3nf and every order id is unique with one product only in order to make this query working i have to make one more table in which we have record for orders and products ordered with them which will make DB more complex.
6.amcdb=# SELECT customer.c_id ,CONCAT(customer.c_fn,’ ’,customer.c_ln) AS fullname ,customer.adress FROM
customer INNER JOIN order ON orderr.c_id=customer.c_id WHERE customer.c_id NOT IN (SELECT
orderr.c_id FROM orderr);
+------+---------------+---------------------------+
| c_id | fullname | adress |
+------+---------------+---------------------------+
| 1452 | lucija marinkovic | 25 zagreb crotia 123456|
+------+---------------+---------------------------+
7.amcdb=# SELECT employee.e_id, employee.e_fn, employee.e_ln, department.d_name,store.s_name FROM store, employee INNER JOIN department ON employee.d_type_id = department.d_id WHERE employee.s_id=store.s_id;
+------+----------+---------+----------+--------+
| e_id | e_fn | e_ln | d_name | s_name |
+------+----------+---------+----------+--------+
| 13 | shane | watson | security | abc |
| 25 | martha | max | finance | abcd |
| 103 | shankar | khosla | accounts | abcde |
| 109 | victoria | ashley | security | bcde |
| 125 | glenn | maxwell | HR | abcd |
+------+----------+---------+----------+--------
8.amcdb=# SELECT store.s_id,store.s_name,COUNT(employee.e_id) AS total FROM employee INNER JOIN store
ON employee.s_id=store.s_id INNER JOIN department ON employee.d_type_id=department.d_id WHERE department.d_name LIKE ‘accounts’;
+------+--------+-------+
| s_id | s_name | total |
+------+--------+-------+
| 13 | abcde | 1 |
+------+--------+-------+
1 row in set (0.000 sec)
9.amcdb=# SELECT o_id,o_name,date from orderr where date BETWEEN ‘2017-06-30’ AND ‘2018-07-01’;
+------+--------+------------+
| o_id | o_name | date |
+------+--------+------------+
| 1008 | phones | 2017-08-25 |
| 1011 | water | 2018-04-10 |
+------+--------+------------+
10.amcdb=# SELECT customer.c_id,CONCAT(customer.c_fn,’ ‘,customer.c_ln)as FULLNAME ,(orderr.o_id)as total FROM customer INNER JOIN orderr ON customer.c_id=orderr.c_id;
+------+----------------+-------+
| c_id | FULLNAME | total |
+------+----------------+-------+
| 1412 | bella fregusan | 1004 |
| 1432 | omar khan | 1005 |
| 1432 | omar khan | 1008 |
| 1632 | abdul ahmed | 1006 |
| 1932 | bella fregusan | 1011 |
+------+----------------+-------+
5 rows in set (0.000 sec)
11.amcdb=# SELECT o_id,o_name,date ,COUNT(p_id)from orderr INNER JOIN products where orderr.p_id=product.p_id;
+------+--------+------------+
| o_id | o_name | date | total |
+------+--------+------------+------------+
| 1008 | phones | 2017-08-25 |2
| 1011 | water | 2018-04-10 |6
+------+--------+------------+-----------+
References
Hackr.io. 2020. Normalization In DBMS: 1NF, 2NF, 3NF And BCNF With Examples. [online] Available at: <https://hackr.io/blog/dbms-normalization> [Accessed 4 June 2020].
Postgresql.org. 2020. Postgresql: Documentation: 9.1: SQL Commands. [online] Available at: <https://www.postgresql.org/docs/9.1/sql-commands.html> [Accessed 4 June 2020].
Educative: Interactive Courses for Software Developers. 2020. What Is A Database Schema?. [online] Available at: <https://www.educative.io/edpresso/what-is-a-database-schema> [Accessed 4 June 2020].
Urgenthomework helped me with finance homework problems and taught math portion of my course as well. Initially, I used a tutor that taught me math course I felt that as if I was not getting the help I needed. With the help of Urgenthomework, I got precisely where I was weak: