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, CA94539

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?

Name

SSN

LocalPhone

Address

CellPhone

Age

GPA

George Shaw William Edwards

123-45-6789

555-1234

123 Main St., Anytown, CA94539

555-4321

19

3.75

Explanation / Answer

1)

a)prefix with 0

b)store the additional information in localphone and cellphone

c)if name is divided into 3 parts then it becomes easy to search a person

d)if size of data is large then split

2)Student ,Course, Enroll

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

---

---

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