1. - Consider a STUDENT relation in a UNIVERSITY database with the following att

Question

1.

- Consider a STUDENT relation in a UNIVERSITY database with the following

attributes (Name, SSN, Local_phone, Address, Cell_phone, Age, GPA). Note that

the cell phone may be from a different city and state (or province) from the

local phone. A possible tuple of the relation is shown below:

Name

SSN

LocalPhone

Address

CellPhone

Age

GPA

George

Shaw William Edwards

123-45-6789

555-1234

123 Main St.,

Anytown, CA

94539

555-4321

19

3.75

a.

Identify the critical missing information from the LocalPhone and CellPhone

attributes as shown in the example above. (Hint: How do call someone who lives

in a different state or province?)

b.

Would you store this additional information in the LocalPhone and CellPhone

attributes or add new attributes to the schema for STUDENT?

c.

Consider the Name attribute. What are

the advantages and disadvantages of splitting this field from one attribute

into three attributes (first name, middle name, and last name)?

d.

What general guideline would you recommend for deciding when to store

information in a single attribute and when to split the information.

2. - Consider the following relations for a database

that keeps track of student enrollment in courses and the books adopted for

each course:

STUDENT (SSN, Name, Major, Bdate)

COURSE (Course#, Quarter,

Grade)

ENROLL (SSN, Course#, Quarter,

Grade)

BOOK_ADOPTION (Course#, Quarter,

Book_ISBN)

TEXT (Book_ISBN, Book_Title, Publisher,

Author)

Specify the foreign keys for this schema, stating

any assumptions you make.

3. Consider the following six relations for an

order-processing database application in a company:

CUSTOMER (Cust#, Cname, City)

ORDER (Order#, Odate, Cust#, Ord_Amt)

ORDER_ITEM (Order#, Item#, Qty)

ITEM (Item#, Unit_price)

SHIPMENT (Order#, Warehouse#,

Ship_date)

WAREHOUSE (Warehouse#, City)

Here, Ord_Amt refers to total dollar amount of an

order; Odate is the date the order was placed; Ship_date is the date an order

(or part of an order) is shipped from the warehouse. Assume that an order can

be shipped from several warehouses. Specify the foreign keys for this schema,

stating any assumptions you make. What

other constraints can you think of for this database?

Explanation / Answer

1.
a) Need area codes for identifying local phones from cell phones adn to call outsiders
b) Store it seperately, as it will be used only to call outsiders
c) Split the name only if we need the info like: siblings, group by lastname, person with middlename "JOHN" etc.
d) Splitting of attribute can be done if there is a limit on length of attribute or to categorize the data based on part of the attribute.
ex: Address can be split so that we can categorize the data based on city/town, street etc

2. Considering only these 5 relations
STUDENT: No foreign key
COURSE: No foreign key
ENROLL: SSN from STUDENT, Course# from COURSE
BOOK_ADOPTION: Course# from COURSE
TEXT: No foreign key

3.
CUSTOMER: No foreign key
ORDER: Cust# from CUSTOMER to identify his/her order
ORDER_ITEM: Item# from ITEM
ITEM: No foreign key
SHIPMENT: Order# from ORDER, Warehouse# from WAREHOUSE
WAREHOUSE: No foreign key

DELIVER or HISTORY (Order#, Del_date) can be added to track previous orders.

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.