CIS 401 Review over Ch 3 and 4

Chapter 3 Topics

Normalize the following table to 4NF

Scenario: You own a small IT consulting firm that employees college student 12 nerds. Clients contact you by phone or email and describe the nature of the project they need completed. You determine how much you will charge the client for the project (BidAmt) and which of your nerds to assign to each project based on each nerd’s technical ability and on the needs of the project. Clients often return after one project is complete and request additional IT work that they need done, so clients can have multiple projects. Each client names a contact person at the firm and provides that person’s phone number in case you need to contact them to check you assumptions during the project. Projects are usually small but at times may require multiple nerds. For example, one project involves a website that queries a database and displays results to the user across the web. This project needs a nerd with web skills assigned to it. It also needs a nerd with SQL skills. A project can have multiple nerds assigned to it. Nerds can be working on multiple projects at the same time. Many of them are specialists, having only one IT skill, but some nerds have multiple skills like web design and C# and SQL.

ITPROJECT(ProjectID, ClientID, EmployeeID, PrjStartDate, PrjDueDate, SkillsID, SkillsDescription, EmpFname, EmpLname, EmpAddr, EmpCity, State, Zip, ClientCompanyName, ClientPtofContact, BidAmt, HoursWorked, DescofWorkDone, Phone)

Assumptions Given:

All our IT employees have skills like: web design, server admin, SQL, Java, VoIP. Most of these employees have multiple skills, like web design AND database administration. The SkillsID is a surrogate key used to identify these skills numerically, while the SkillsDescription field is used to describe the skill, like “database administration”.

Regardless of the hourly rates paid to each nerd, and the hours worked on each project, the price charged to the client is the original quoted price (bidamt). It is important however, for you to keep track of the number of hours each employee spends on each project so you can determine at the end whether or not your company made any money. If you charge client less than you paid your nerds then you’re losing money. Decide for yourself how you will keep records of the cost of each project. Be sure your design also helps you know how much to pay your nerds on pay day. You may need to make up new columns for this since I have not provided any. Lets assume for now that all nerds are paid by the hour rather than salaried.

B. Assumptions Added by You:

C. List Functional dependencies:

D. Show each table with each of its attributes, properly formatted. (PKs, FKs, etc.) Use additional paper from the printer if necessary.

E. Referential Integrity Constraints.

2. Write out the BCNF quote.

3. You have been given the following database tables for potential redesign. Write the SQL statements to confirm whether a referential integrity constraint has been enforced between these two tables.

Boat owners pay a monthly rental amount to park their boat at a certain marina on Lake Travis.

MARINA(MarinaID, MgrFName, MgrLName, SlipRentalRate)

BOATOWNER(OwnerID, BoatIDNumber, OwnerFName, OwnerLName, OwnerEmail, Phone1, Phone2,LeaseExpirationDate, MarinaID)

4. A NULL value in BoatIDNumber (in BOATOWNER table above) could indicate three possibilities, list them and describe them with specific examples. Should this attribute continue to allow NULL values? (Yes or No) Defend your answer. (5 points)

5. In the MARINA and BOATOWNER tables above, at least two columns MUST be checked for NULLs. Write the SQL statements to check for NULLS on each of these two columns.

6. The author recommends that other types of columns SHOULD be checked for nulls. Which column in the MARINA/BOATOWNER example would you also recommend checking for NULLS besides the two required in the previous question. ____________________Explain why it is important not to allow nulls in this column.