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:

supplier table

product table

store table:

orderr table

department table

employee table:

payslip table

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 |

+------+--------+-------------------+----------------+----------+------------+-----------+

5. 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].