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

Australia Universities

ACT

Australian Catholic University

Australian National University

Bond University

Central Queensland University

Charles Darwin University

Charles Sturt University

Curtin University of Technology

Deakin University

Edith Cowan University

Flinders University

Griffith University

Holmes Institute

James Cook University

La Trobe University

Macquarie University

Monash University

Murdoch University

Queensland University of Technology

RMIT University

Southern Cross University

Swinburne University of Technology

University of Adelaide

University of Ballarat

University of Canberra

University of Melbourne

University of Newcastle

University of New England

University of New South Wales

University of Notre Dame Australia

University of Queensland

University of South Australia

University of Southern Queensland

University of Sydney

University of Tasmania

University of Technology Sydney

University of the Sunshine Coast

University of Western Australia

University of Wollongong

Victoria University

Western Sydney University

Year 11 - 12 Certification Assignment

Australian Capital Territory Year 12 Certificate

HSC - Higher School Certificate

NTCE - Northern Territory Certificate of Education

QCE - Queensland Certificate of Education

SACE - South Australian Certificate of Education

TCE - Tasmanian Certificate of Education

VCE - Victorian Certificate of Education

WACE - Western Australia Certificate of Education

ISY1002/ISY103 Database Management for Business Assignment Help

Part A:

References

Diploma Universities Assignments