ICT285 PASS Week 13

CUSTOMER (CustomerNumber, CustomerName, DateOfBirth)

EMPLOYEE (EmployeeNumber, EmployeeName, DateOfBirth)

SALE (SaleID, CustomerNumber, EmployeeNumber, Cost)

What is the CustomerName for SaleID='1111'? Reorder the following steps.

• PROJECT _CustomerName (JOINED)

• CUSTOMER* _{CUSTOMER.CUSTOMERNUMBER = SALE.CUSTOMERNUMBER} RES_SALE

• RESTRICT _{SaleID='1111'} (SALE)

SALESPERSON(SPN, Name, StartYear, DeptNo)

TRIP(TripID, ToCity, DepartDate, ReturnDate, SPN)

EXPENSE(TripID, Account#, Amount)

Give the TripID for all trips by salesperson 'Steve'. Reorder the following steps.

• RESTRICT _Name='Steve' (SALESPERSON)

• RES_SALE* _{RES_SALE.SPN = TRIP.SPN} TRIP

• PROJECT _TripID (TRIP)

SALESPERSON(SPN, Name, StartYear, DeptNo)

TRIP(TripID, ToCity, DepartDate, ReturnDate, SPN)

EXPENSE(TripID, Account#, Amount)

Give the names of sales people that have NOT gone to Sydney. Reorder the following steps.

• PROJECT Name (JOINED) -> ALL_SYD_NAMES

• SALESPERSON* _{SALESPERSON.SPN = TRIPS_SYDNEY.SPN} TRIPS_SYDNEY -> JOINED

• PROJECT _Name (SALESPERSON) -> ALL_NAMES

• RESTRICT _{ToCity='Sydney'} (TRIP) -> TRIPS_SYDNEY

• ALL_NAMES MINUS ALL_SYD_NAMES

PROPERTY (PropertyNo, Address, NumberOfRooms, OwnerNo)

OWNER (OwnerNo, FamilyName, GivenNames, Address)

List the family names for owners that own properties with more than 3 rooms. Reorder the following steps.

• RESTRICT NumberOfRooms > 3 (PROPERTY)

• PROJECT FamilyName (RES_JOINED)

• OWNER* _{OWNER.OwnerNo=RES_3.OwnerNo} RES_3

PROPERTY (PropertyNo, Address, NumberOfRooms, OwnerNo)

OWNER (OwnerNo, FamilyName, GivenNames, Address)

List the family names for owners that do not own properties. Reorder the following steps.

PROJECT OWNER TABLE FIRST.

• PROJECT _FamilyName (ALL_NON_OWNERS)

• PROJECT _OwnerNo (OWNER) -> ALL_OWNERS

• ALL_OWNERS MINUS ALL_PROP_OWNERS -> ALL_NON_OWNERS

• PROJECT _OwnerNo (PROPERTY) -> ALL_PROP_OWNERS

What normal form is this table in?

• Invalid

• 1NF

• 2NF

• 3NF

What normal form is this table in?

• Invalid

• 1NF

• 2NF

• 3NF

What normal form is this table in?

• Invalid

• 1NF

• 2NF

• 3NF

What normal form is this table in?

• Invalid

• 1NF

• 2NF

• 3NF

What normal form is this table in?

• Invalid

• 1NF

• 2NF

• 3NF