Reference no: EM132489871

INFS 4019 Relational Databases and Data Warehouses - University of South Australia

Database Design and implementation

Problem statement

The assignment has the following parts.

Part (1) Describe a database application that you know of. Example applications can be for medical clinics, shops, government departments, schools, factories, companies, transportation, real estate, etc. The size of the application should have at least 3classes not including sub classes and transaction classes. A transaction class describes a relationship like an association class does or a class that is equivalent to an association class. An example is given in theappendix of this specification. A builder database application below is given to you as an example.

The application must have reasonable complexity in terms of many-to-many relationships, association classes, and/or inheritance to get full marks. If the classes are not linked logically or the relationships are very simple, a moderation to the full mark will be applied.

The writing of the description must be cohesive and logically correct.
Part (2) Draw a UML diagram for the application to show classes and relationships, indicate candidate keys (CKs) and multiplicities whenever appropriate. This diagram is critical and if it does not capture all the requirements and the constraints of the application, the final database designed will not be complete.

Part (3) Convert the UML diagram into a relational database schema; primary keys and foreign keys have to be clearly identified.

Part (4) Draw the tables and add sample data for the tables. The number of tuples of your sample data depends on the complexity of your keys and foreign keys. The tuples should be enough to demonstrate various possible values for the constraints.

A builder database application: A building company (builder) likes to develop a database to manage its data.

A client (with driver's license number, name, address, telephone, email) of the builder requests to establish a building plan (design) for a location (address, site-description). The plan is identified by a number and has an overall building area, the foundation size and depth, the number of levels, etc. It comprises of some job items (like bedrooms, kitchen, toilets, foundation, frames, walls, ceiling, roofing, electricity, air conditioner, windows, doors, stairs, elevators, pipes). Each item has a description of materials and a cost.

After the plan is established, the client may sign a contract with the builder for the plan to be built. When signing the contract, the client needs to select utensils (lights, carpet, paints for rooms, tiles etc.) for the building. Utensils are identified by a serial number and has attribute of brand, colour, size, weight, etc. Utensils with the same type and size for the same purpose often have different prices if they are from different suppliers (name, address, telephone).

Different job items of a building are often constructed by different contractors (electrician, carpenter, plumber etc). Each contractor has a license number, name, address, contract history with the company. As the building jobs proceed, the system recordsthe job item, the start date, end date, and the assessor, and description of assessments.

The design of the building is free if the client proceeds to get it built by the builder. Otherwise, a fee will be charged on the client and the company tracks the payment details. The payment for the building will be made in instalments and the company will track the payments of these instalments too.

The records for the contracts need to be endorsed by two of the company employees. Each employee in the organization have a name, address, username, email, and telephone and working history with the company.

(End of the builder application)

Requirements

Part (1) You submit only a word document as your solution. All answers must be included in this file. Your answers must be in the order of the parts in the Problem Statement above. A template report is given.

Part (2) Your UML diagram must be readable. The size of the words in the diagram should not be smaller than 9pt. If your diagram is drawn using a tool, insert the resulting diagram as an image into the word document. You can hand-draw the UML diagram and then scan and insert it into the Word file. However, the hand drawing must be clearly readable. If it is not readable, it is not marked. The resolution of the scanned image must be NO more than 300 dpi grayscale. Otherwise, your file is too big to be submitted.

Part (3) Your UML design must be the one for the described application and you should not invent classes and attributes that are not in the description unless a justification is given. That is, consistency between the description and the UML is an important area to be checked. At the same time, if your UML design does not capture the full design requirements and constraints, your answers for other parts will be affected.

Part (4) UML classes should NOT contain Foreign Keys (FKs) attributes. In UML, relationships are represented by lines between classes, not by attributes although you may have role names.

Part (5) The format of table schemas should follow our logic design exercise. Each table schema in the logic design is put in a separate line and the specifications of the primary key, candidate keys, and foreign keys of the table must follow the table schema immediately, be indented and be presented in different lines. The logic design of the booking table in the hotel database is shown below as an example.

If your logic design does not agree with your UML design, for example, an attributeis in the logic design but not in the UML diagram, heavy deduction up to 50% will apply unless otherwise you justify.

Part (6) The sample data for the tables must be demonstrative. The tuples for a table must be different in some ways and can show the 1-1, 1-m, m-m types of relationships whenever applicable.

Only discussions at the general level without involving specific applications and code/solutions are allowed in the discussion board. For example, if you need help with the use of ‘having' clause, you find an example from the lecture slides about ‘having' and ask questions based on the example for a discussion.

Attachment:- Database Design and implementation.rar