[/I]Hi all!
I'm quite new with databases. Need some help on the case study below for my assignment:
ABC is a successful business providing design and drafting services to the local construction industry. With rapid growth over recent years, owner Tony Daley can no longer track active projects in his head. Consequently, Tony has decided to develop a new database application to help him run the business. As well as helping with project management, the new application will be used to introduce a system of productivity bonus payments to staff.
Clients of ABC range from families building their first home through to long-standing clients who build commercial premises. Some clients arrive with a detailed design that is ready for a set of plans to be drafted and submitted to council. Other clients arrive with only a vague idea of the building they wish to construct. Client details that need to be recorded in the new database include FirstName, LastName, StreetAddress (e.g. “123 Main Street”), Suburb, State, Postcode, and PhoneNumber.
All projects undertaken by ABC will be recorded in the new database. Details that need to be recorded include Description, StartDate, DueDate, PriceType (fixed or variable), QuotedPrice, FinalPrice, Status, DateCompleted and a unique ProjectNumber. For each project, Tony assigns one senior employee as project manager. With fixed-price projects, there is a commitment to complete the project for the quoted price.
Technical staff at ABC are employed in one of 5 positions: junior drafter, professional drafter, senior drafter, junior designer and senior designer. Tony believes that the productivity of technical staff may improve if he pays a quarterly bonus that reflects individual productivity. To do this, Tony needs to record the number of hours each employee works on activities that can be billed to client projects during each quarter (HoursBilledInQuarter). A bonus is paid if an employee reaches the base number of billing hours (BaseBillingHoursPerQuarter) required by the employee’s position. The bonus is calculated as a percentage of annual salary (BaseSalary) for the employee’s position. If HoursBilledInQuarter reaches BaseBillingHoursPerQuarter, the employee receives 2% of BaseSalary as a bonus. If HoursBilledInQuarter exceeds the BaseBillingHoursPerQuarter, a higher bonus is paid.
Project planning is assisted by identifying the services required by a project. A simple project may require only four services - initial consultation, design selection, drafting and council approval; a complex project will require many more services. Project management is assisted by monitoring services that have not been completed. Service details that need to be recorded in the new database include Description (e.g. initial consultation), StartDate, DueDate, PlannedPrice, ActualPrice, Status, and DateCompleted.
A fixed-price project has a fixed set of fixed-price services. The client of a fixed-price project is charged the PlannedPrice for a service. The client of a variable-price project is charged the ActualPrice for a service - obtained by summing the ActualPrice of activities undertaken to deliver that service.
More than one employee can undertake activities that contribute to the delivery of a service. For example, a senior drafter may supervise the work of a junior drafter to deliver a drafting service. In this case, drafting is one activity and drafting supervision is another. Details of activities that need to be recorded in the new database include Description (e.g. drafting), StartDate, DueDate, PlannedPrice, HoursWorked, BillingRate, ActualPrice, Status, and DateCompleted.
ABC use the MYOB accounting system to manage invoices and payments. The project manager decides when an invoice is raised against a project. An invoice will bill the client for completed services that have not previously been invoiced. Invoice details that need to be recorded in the new database include the MYOBInvoiceNumber, InvoiceDate, InvoiceTotal and the set of services billed on that invoice. The new application will communicate with the MYOB application to generate invoices. Having invoiced a service, the application must prevent users from changing any details of that service, or related activities. Project invoices are generated at the end of a working day to avoid invoicing a project more than once on the same day.
Case Study Notes
• The status of a project, service or activity is one of: planned, active or completed.
• A history of bonus payments must be recorded in the new database.
• A history of previous staff who worked on projects must be recorded; you must decide if any additional attributes should be recorded to support this feature.
• A project can have several services with the same Description (e.g. client consultation).
• A service can have several activities with the same Description (e.g. drafting).
• Tony may need to record details of a new client before creating a project for that client.
• The current BillingRate for each employee must be recorded in the new database.
• The calculation of productivity bonuses will occur after the close-of-business on the last day of each quarter; the process will mark each active activity as completed and create a replacement active activity with a StartDate of the following day.
I've come up with seven entities / tables so far which are:
CLIENT{ClientID, Firstname, Lastname, StreetAddress, Suburb, State, Postcode, PhoneNumber}
PROJECT{ProjectNumber, Description, Startdate, Duedate, Pricetype, Quotedprice, FinalPrice, Status, DateCompleted}
STAFF{StaffID, Firstname, Lastname, Contact#, BillingRate, Position}
PROJECT MANAGER (because each project is assigned to each project manager who also issues the invoice; I'm not quite certain if I should put the manager as a staff as well but according to the context, PM isnt a staff - pls comment)
SERVICE TYPE {ServiceNumber, Description, StartDate, DueDate, PlannedPrice, ActualPrice, ServiceStatus, DateCompleted,HoursBilledinQuarted} (again, not quite sure if HoursBilledinQuarted should be here or in Project)
BONUS (I dont know which entities should I put in bonus, or should this just be part of STAFF Entitiy? However, not all staff can be eligible for the bonus)
MYOB {MyobInvoice#, InvoiceDate, InvoiceTotal
Pls comment if I'm actually planning the database correctly. Would really appreciate your help. Thanks much!
