computers science

Question 1

a. A company operates four departments. Although the company operates four departments now, it may later decide to combine operations or to expand them. Clearly, the company operates at least one department.

b. Each department in part (a) employs employees. To exist, a department must have at least one employee. To simplify payroll procedure, an employee is likely to be assigned to just one department. How many people must be involved in a given operation before a department can be formed? The answer to that question depends on company policy, which dictates the appropriate business rule. Thus the business rule(s) will ultimately decide the cardinalities.

c. Each of the employees in part b may or may not have one or more dependents. Since an employee is not required to claim a dependent, DEPENDENT is optional to EMPLOYEE. Remember a company cannot permit the existence who is not claimed by an employee.

d. Each employee is part (c) may or may not have an employment history. Each employee may have an employment history. If a newly hired employee has no prior work experience, the employee will not have an employment history, so the EMP_HIST is optional to EMPLOYEE. An employee may have worked for more than one previous employer or may have worked in several other departments prior to the current assignment.

Answer:

DEPARTMENTS

(1,N) (1,1)

COMPANY

(1,1)

(1,N)

(0,N)

DEPENDENT

(0,1)

EMP_HIST

(0,N)

(1,N) Jegan S Jegan S (சு. ஜெகன்)P

EMPLOYEE

Question 2

a) How many records does the file contain, and how many fields are there per record?

There are 4 records and five fields.

b) What problem would you encounter if you wanted to produce a report that would list the cities?

How would you solve this problem by changing the file structure?

This problem can be solved by adding three more attributes such as

(Project_Code, Project_Manager, Manager_Address)

c) If you wanted to produce a listing of the file contents by last name, area code, city, state, or ZIPCODE, how would you alter the file structure?

PROJECT_CODE,FNAME,LNAME,AREA_CODE,PHONE_NO,CITY,STATE,

ZIP_CODE.

It contained multivalued attributes, which have to be normalized.

d) What data redundancies do you detect, and how could these redundancies lead to anomalies?

e) Create the relational schema by using two relational database tables, PROJECT and MANAGER, eliminate the redundancies discovered in Problem 2d.

Question 3

a) Give an example of a table with a multi-valued attribute?

Employee table has multivalued attributes for example Address, Phone Number.

b) Create a relation schema containing a composite attribute?

Apartment_Number

Street

Number

Street_ Address

Zip

State

City

Address

c) Create a relational schema containing a weak entity?

EMPLOYEE

Question 4

Consider the following six relations for and order-processing database application in a company:

CUSTOMER (City, CustNo., CName)

ORDER (Order#, Odate, CustNo., Ord_Amt)

ORDER_ITEM (Order#, ItemID, Qty)

ITEM (ItemID, Unit_Price)

SHIPMENT (Order#, WarehouseID, Ship_date)

WAREHOUSE (City, WarehouseID)

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 is shipped from the warehouse. Assume that an order can be shipped from several warehouses.

a. Specify Primary keys for this schema,

CUSTOMER (City, CustNo., CName)

ORDER (Order#, Odate, CustNo., Ord_Amt)

ORDER_ITEM (Order#, ItemID, Qty)

ITEM (ItemID, Unit_Price)

SHIPMENT (Order#, WarehouseID, Ship_date)

WAREHOUSE (City, WarehouseID)

b. Specify foreign keys for this schema, stating any assumptions you make.

CUSTOMER (City, CustNo., CName)

ORDER (Order#, Odate, CustNo., Ord_Amt)

ORDER_ITEM (Order#, ItemID, Qty)

ITEM (ItemID, Unit_Price)