1. Consider the table: STUDENT (StudentNumber, StudentName, Dorm, RoomType, DormCost, Sibling, Nickname)
Assume that students pay different dorm costs, depending on the type of room they have, but that all members of a club pay the same cost.
Assume that students can have multiple nicknames.
Transform this table into two or more tables such that each table is in BCNF and in 4NF.
State the primary keys, candidate keys, foreign keys, and referential integrity constraints.
2. Consider the following database.
EMPLOYEE(Name, SSN, Salary, DNO, SupervisorSSN, JobCode)
DEPARTMENT(DNO, TotalSalary, ManagerSSN)
STARTING_PAY(JobCode, StartPay)
Note that Dept_No in EMPLOYEE table is foreign key.
Based on Active Database Concepts, answer the following questions:
Write the active rules (triggers) for following two events
- Limit all salary increases to 50%.
- All new hires for a given job code get the same starting salary, which is available in the STARTING_PAY table.
2) Explain Hybrid fragmentation