ISYS224 Database Systems Assignment 1

This assignment for ISYS224 Database Systems involves designing and implementing a database for Sydney Book House (SBH). The solution details an Entity Relationship Diagram (ERD) with seven entities: Branch, Author, Publisher, Books, Employee, Work_shift, and Emp_shift. The relationships are designed to be in 3rd Normal Form (3NF). The document provides SQL code for creating the tables, inserting sample data, and executing several queries. The queries demonstrate retrieving book titles and prices, joining tables to retrieve author information, counting books per publisher, and retrieving book and branch details. The solution also includes references to support the 3NF design.

Assignment – 1 1
ISYS224 Database Systems 2017
(Assignment – 1)
Submitted By
Course
Professor
Date

Assignment – 1 2
Introduction
As per the scenario, the assignment is for Sydney Book House (SBH). There are four types of
data which needs to be saved in the tables like author details, branch details, publisher details and
book details. Every book needs to be published by a publisher that is list saved in the database.
There must be at least one author for every book. It can be possible that author writes more than
one book. A branch can have more than one book that is saved in database. There must be an
option or we can say table that can store employee data.
Entity Relationship Diagram
According to the scenario, I have taken seven entities for saving the entire data for Sydney Book.
Branch, Author, Employee, Work_shift will be master tables which will be used to save data of
Branch, Authors, Available Shifts and employees details. On the other hand, Book, Emp_shift
will be child tables which will reference all the master tables. An author can write more than one
book and that detail is saved in books table. There can be more than one book in a branch and
that details are also saved in Books table. The relationship exist in 3NF.

Assignment – 1 3
Entities
Branch  BranchID, Branch_address, Branch_city, Branch_phone, No_of_emp
Author  AuthorID, Author_name, Author_contact, Author_email

Assignment – 1 4
Publisher  PublisherID, Pub_name, Pub_city, Pub_contact
Books  BookID, Book_title, Book_price, Paperback, AuthorID, PublisherID, BranchID
Foreign key  AuthorID references author, PublisherID references publisher, BranchID
references Branch.
Employee  EmployeeID, emp_name, emp_type, emp_desg, Supervisor.
Work_shift  ShiftID, Week_day, Start_time, end_time, duty_type
Emp_Shift  ID, EmployeeID, ShiftID, BranchID
Foreign key  EmployeeID references employee, ShiftID references work_shift, BranchID
references branch.
In the above relationships, all the relations are in 3NF. A 3NF relationship is possible when the
relation is already in 2NF and there is no transitive dependency. (1keydata, 2015). When Books
is connected with Publisher, Author and Branch it defines 3NF relationship. As primary keys are
already defined in all the tables and no transitive dependency exists in the defined relationship,
so we can say that relationship exists in 3NF.
Creating Tables
Drop database if exists sydney_book_house;
Create databse sydney_book_house;
use sydney_book_house;
1. Branch

Assignment – 1 5
create table branch (branchid varchar(3) primary key, branch_address varchar(20), branch_phone
int(10), no_of_emp int(3));
2. Author
create table author (authorhid varchar(3) primary key, author_name varchar(20), author_contact
int(10), author_email varchar(30));
3. Publisher
create table publisher (publisherid varchar(3) primary key, pub_name varchar(20), pub_city
varchar(20), pub_contact int(10));
4. Books
create table books (Bookid varchar(5) primary key, book_title varchar(30), book_price
float(10,2), paperback char(1), authorid varchar(3) references author(authorid), publisherid
varchar(3) references publisher(publisherid), branchid varchar(3) references branch);
5. Employee
create table employee (employeeid varchar(5) primary key, emp_name varchar(30), emp_type
varchar(10), emp_desg varchar(20), supervisor varchar(30));
6. Work_shift
create table work_shift (shiftid varchar(3) primary key, week_day varchar(15), start_time time,
end_time time, duty_type varchar(20));
7. Emp_shift

Assignment – 1 6
create table emp_shift (id varchar(3) primary key, employeeid varchar(3) references
employee(employeeid), shiftid varchar(3) references work_shift(shiftid), branchid varchar(3)
references branch (branchid));
Inserting Values
1. Branch
Insert into branch values ('B01', 'Sydney', 9999999999, 5);
Insert into branch values ('B02', 'Melbourne', 777777777, 6);
Insert into branch values ('B03', 'jgjfgj', 454545454, 7);
Insert into branch values ('B04', 'Sydney', 675764464, 8);
Insert into branch values ('B05', 'sdjsfj', 3575866696, 9);

Assignment – 1 7
2. Author
Insert into Author values ('A01', 'John Smith', 565656556, '[e-mail]');
Insert into Author values ('A02', 'Jackson', 676788, '[e-mail]');
Insert into Author values ('A03', 'Mcleod', 78778787, '[e-mail]');
Insert into Author values ('A04', 'Anderson', 57578955, '[e-mail]');
Insert into Author values ('A05', 'Humaun', 657789966, '[e-mail]');

Assignment – 1 8
3. Publisher
Insert into publisher values ('P01', 'ABCDEF', 'Sydney', 34747474);
Insert into publisher values ('P02', 'AAAAAA', 'Melbourne', 6888666);
Insert into publisher values ('P03', 'BBBBBB', 'Sydney', 35757586);
Insert into publisher values ('P04', 'CCCCCC', 'Melbourne', 68999797);
Insert into publisher values ('P05', 'DDDDDD', 'Sydney', 67888888);
4. Books

Assignment – 1 9
5. Employee
Insert into employee values ('E0001', 'Jackson', 'Permanent', 'Supervisor', ' ');
Insert into employee values ('E0002', 'Jahnson', 'Contract', 'Staff', 'Jackson ');
Insert into employee values ('E0003', 'Smitha', 'Permanent', 'Staff', 'Robbin ');
Insert into employee values ('E0004', 'Samriti', 'Contract', 'Staff', 'Jackson ');
Insert into employee values ('E0005', 'Robbin', 'Permanent', 'Supervisor', ' ');
6. Work_shift
Insert into work_shift values ('S01', 'Monday', '9:00', '5:00', 'Sales');
Insert into work_shift values ('S02', 'Tuesday', '20:00', '8:00', 'Cleaning');
Insert into work_shift values ('S03', 'Wednesday', '9:00', '5:00', 'Sales');
Insert into work_shift values ('S04', 'Thursday', '9:00', '5:00', 'Sales');

Assignment – 1 10
7. Emp_Shift
Insert into emp_shift values ('1', 'E0001', 'S01', 'B01');
Insert into emp_shift values ('2', 'E0002', 'S02', 'B02');
Insert into emp_shift values ('3', 'E0003', 'S03', 'B01');
Insert into emp_shift values ('4', 'E0004', 'S01', 'B03');
Insert into emp_shift values ('5', 'E0005', 'S01', 'B02');

Assignment – 1 11
Queries
SELECT book_title, book_price from books where book_type='History'
SELECT books.book_title, book_price, books.book_type, author.author_name, author_email
from books, author where books.authorid=author.authorhid and books.book_type='Fiction';

Assignment – 1 12
select count(books.bookid), publisher.pub_name from books, publisher where
books.publisherid=publisher.publisherid group by books.bookid, publisher.pub_name;
select books.Bookid, books.book_title, books.no_of_copies, branch.branch_address
from books, branch where
books.branchid=branch.branchid;

Assignment – 1 13
Reference
Third Normal Form (3NF). 1keydata (2015).
http://www.1keydata.com/database-normalization/third-normal-form-3nf.php