Ask a question from expert
ISYS224 Database Systems 2017 Assignment
13 Pages1356 Words305 Views
Added on 2019-11-19
ISYS224 Database Systems 2017 Assignment
Added on 2019-11-19
BookmarkShareRelated Documents
Assignment – 11ISYS224 Database Systems 2017(Assignment – 1)Submitted ByCourseProfessorDate
Assignment – 12IntroductionAs per the scenario, the assignment is for Sydney Book House (SBH). There are four types ofdata which needs to be saved in the tables like author details, branch details, publisher details andbook 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 thanone book. A branch can have more than one book that is saved in database. There must be anoption or we can say table that can store employee data. Entity Relationship DiagramAccording 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 ofBranch, Authors, Available Shifts and employees details. On the other hand, Book, Emp_shiftwill be child tables which will reference all the master tables. An author can write more than onebook and that detail is saved in books table. There can be more than one book in a branch andthat details are also saved in Books table. The relationship exist in 3NF.
Assignment – 13EntitiesBranch BranchID, Branch_address, Branch_city, Branch_phone, No_of_empAuthor AuthorID, Author_name, Author_contact, Author_email
Assignment – 14Publisher PublisherID, Pub_name, Pub_city, Pub_contactBooks BookID, Book_title, Book_price, Paperback, AuthorID, PublisherID, BranchIDForeign key AuthorID references author, PublisherID references publisher, BranchIDreferences Branch.Employee EmployeeID, emp_name, emp_type, emp_desg, Supervisor.Work_shift ShiftID, Week_day, Start_time, end_time, duty_typeEmp_Shift ID, EmployeeID, ShiftID, BranchIDForeign key EmployeeID references employee, ShiftID references work_shift, BranchIDreferences branch.In the above relationships, all the relations are in 3NF. A 3NF relationship is possible when therelation is already in 2NF and there is no transitive dependency. (1keydata, 2015). When Booksis connected with Publisher, Author and Branch it defines 3NF relationship. As primary keys arealready defined in all the tables and no transitive dependency exists in the defined relationship,so we can say that relationship exists in 3NF. Creating TablesDrop database if exists sydney_book_house;Create databse sydney_book_house;use sydney_book_house;1.Branch
End of preview
Want to access all the pages? Upload your documents or become a member.
Related Documents
Computer Science Assignment: PHP and MySQL - Deskliblg...
|19
|2163
|111