Normalization- database

User Generated

onfobfn

Computer Science

Description

i have attached the homework paper. we have to draw dagram and answer 3 question for both section one and two.

please look at it and let me know if you have any questions

Unformatted Attachment Preview

Database Programming & Design A) Consider the following universal relation that holds information about books in a bookstore: BOOKS (TITLE, ISBN_10, ISBN_13, AUTHORID, AUTHOR_NAME, PUBLISHER_NAME, PUBLISHER_ADD, TOTAL_COPIES_ORDERED, COPIES_IN_STOCK, COPIES_SOLD, PUBLICATION DATE, CATEGORY, TYPES_AVAIL, SELLING_PRICE, COST, PROFIT_PERCENT) Assume that: • The ISBN identifies a book uniquely (both 10 and 13). (It does not identify each copy of the book, however). • A book can have more than one author. • An author may have more than one book. • Each publisher name is unique. Each publisher has one unique address, the address of the firm's headquarters. • Book titles are not unique. • TOTAL_COPIES _ORDERED is the number of copies of a particular book that the bookstore has ever ordered, while COPIES_IN_STOCK is the number still unsold in the bookstore. • Each book has only one publication date. A revision of a book is given new ISBN Numbers (10 & 13). • The category may be Biography, Science fiction, poetry … (or some combination of them). The title alone is not sufficient to determine the category. • TYPES_AVAIL lists the formats available for the book (Hard Cover, Soft Cover, Kindle, …) • The SELLING_PRICE, which is the amount the bookstore charges for a book, is always the PROFIT_PERCENT above the COST, which is the amount the bookstore pays the publisher or distributor for the book. 1. 2. 3. Using these assumptions and stating any other valid assumptions you need to make, draw out a FD diagram for the above. List out all the other assumptions you make. Write out the relations (with attributes) that you need to use so that they are all normalized (4NF). For each relation, list out the candidate keys, the primary key and any foreign keys. --------------------------------------------------------------------------------------------------------B) Consider the following relation that stores information about students living in dormitories at a college: COLLEGE (STUNAME, STUID, HOMEADD, HOMEPHONE, DORMROOM#, DORMNAME, DORMCODE, ROOMMATE_NAME(s), DORMADD, STATUS, MEALPLAN, ROOMCHARGE, MEALCHARGE, ROOM_TYPE, MEALS_EATEN, MEALS_LEFT, MAJOR) Assume that: • Each student is assigned to one dormitory room. Names of students are not unique. • The room type can be single, double or triple. (A student can have 0, 1 or 2 roommates.) • The college has several dorms. DORMROOM has two parts - a single letter code for the dorm and the number of the particular room assigned to the student. For example, A221 means Adams Hall, room 221. Dorm codes and names are unique. • The DORMADD is the address of the dorm building. Each dorm has its own unique address. For example, Adams Hall may be 123 Main Street, Anytown, NY 10001. • STATUS specifies the student's status: Freshman, Sophomore, Junior, Senior, or Graduate Student. • MEALPLAN specifies how many meals per semester the student has chosen. Each meal plan has a single MEALCHARGE associated with it. • The ROOMCHARGE is different for different dorms, but all students in the same dorm pay the same amount. • A student can have one or more MAJORs. For this example, answer parts 1 – 3, as in Part A.
Purchase answer to see full attachment
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

Explanation & Answer

HelloPFA

TITLE ISBN_10 ISBN_13 AUTHORID AUTHOR_NAME

PUBLISHER_NAME

PUBLISHER_ADD

TOTAL_COPIES_ORDERED

COPIES_IN_STOCK

COPIES_SOLD PUBLICATION DATE

CATEGORY

TYPES_AVAIL

SELLING_PRICE

COST PROFIT_PERCENT

STUNAME STUID HOMEADD HOMEPHONE DORMROOM#

DORMNAME DORMCODE ROOMMATE_NAME

DORMADD STATUS MEALPLAN ROOMCHARGE

MEALCHARGE ROOM_TYPE MEALS_EATEN

MEALS_LEFT MAJOR


Database Programming and Design
Question A
The universal relation of the books in a bookstore is given as,
BOOKS (TITLE, ISBN_10, ISBN_13, AUTHORID, AUTHOR_NAME, PUBLISHER_NAME,
PUBLISHER_ADD, TOTAL_COPIES_ORDERED, COPIES_IN_STOCK, COPIES_SOLD,
PUBLICATION DATE, CATEGORY, TYPES_AVAIL, SELLING_PRICE, COST,
PROFIT_PERCENT)
1. Additional assumptions are,
• AUTHORID is unique for each author.
Based on the assumptions and the following additional assumptions, the FD or Functional Dependencies
are,
ISBN_10  Title, ISBN_13, PUBLICATION DATE, PUBLISHER_NAME
ISBN_13  Title, ISBN_10, PUBLICATION DATE, PUBLISHER_NAME
AUTHORID  AUTHOR_NAME
PUBLISHER_NAME  PUBLISHER_ADD
PUBLISHER_ADD  PUBLISHER_NAME
ISBN_10, TYPES_AVAIL, PUBLISHER_NAME  PROFIT_PERCENT
ISBN_13, TYPES_AVAIL, PUBLISHER_NAME  PROFIT_PERCENT
ISBN_10 > Category
ISBN_10 > TYPES_AVAIL
(ISBN_10, TYPES_AVAIL)  SELLING_PRICE, COST
ISBN_13 > Category
ISBN_13> TYPES_AVAIL
(ISBN_13, TYPES_AVAIL)  SEL...


Anonymous
I was having a hard time with this subject, and this was a great help.

Studypool
4.7
Trustpilot
4.5
Sitejabber
4.4

Similar Content

Related Tags