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
email	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
email	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
email	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
email	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;

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

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

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

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;

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

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

| 3 | beauty | 5600 | 1200 | 6720000 |

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

4.amcdb=# SELECT * FROM store WHERE adress LIKE ‘%george%’;

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

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

| 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 |

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

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

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;

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

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

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

Assignment Help Australia

Australia University Assignments

Not the Exact Question you were looking for ? Post your question for assignment help and get instant help on your homework and assignment questions from our experts