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)
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.
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)