Reference no: EM132497662

Database Design and implementation

Preliminary information

This assignment requires you to apply your knowledge of conceptual database design, logic database design, and table implementation in the Oracle DBMS.

This is an individual assignment. Because of the coronavirus, group work is not possible.

Problem statement

The assignment has the following parts.

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

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

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

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

Requirements

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

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

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

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

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

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

(7) Plagiarism is handled in a lengthy and stressful process by the school involving many parties, which often leads to a severe consequence. Do not commit plagiarism. Students A and B both commit plagiarism/academic misconduct if A gives B significant help in planning or composing a graded solution, or A copies a graded solution from B. You avoid plagiarism by planning and writing/typing your own solution independently and by securing your solution so it is not copied by others.

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