Call/WhatsApp: +1 914 416 5343

DATABASE DESIGN AND IMPLEMENTATION ASSIGNMENT

This assignment is due in week 11, at The assignment should be submitted in electronic form in the PDF format via UTSOnline Turnitin Assignments before the above deadline. Only a single submission per group will be accepted. The file name should have the format StudentNo1_StudentNo2 (i.e. concatenation of the student numbers of both students in the group). The title page of the assignment must contain:

1) Subject name and number

2) Assignment title

3) Names and student numbers of group members
4) Assignment due date

5) Oracle username where the assignment was implemented (do not include your password)

Refer to the subject outline for the academic standards statement. General assignment feedback will be provided in the lecture and individual feedback via UTSonline. Ask in the lecture if you need any clarifications of the assignment. Late assignments will not be accepted.

This assignment supports the following subject objectives: (4) students should be able to show working knowledge of object-relational features of the SQL standard as implemented in Oracle DBMS, (5) students should be able to explain object-relational database design issues and tradeoffs, and (8) students should be able to show ability to communicate in the form of a structured report. The assignment is a team effort; each group will have 2 students. The mark of a team member will be weighted according to their individual contribution as assessed by their peer in the team. If you feel that the other group member is not contributing, the subject coordinator should be informed to find a solution. In extreme cases the group may be dissolved. No complaints about group operation will be considered after the assignment has been handed in. The assignment accounts for 50% of the marks for the subject and should represent a substantial effort (approximately 50 hours of work for each group member). Assignments in this subject should be your own original work. The inclusion in assessable work of any material such as code, graphics or essay text obtained from other persons or sources without citation of the source is plagiarism and is a breach of University Rule 16.2.2. Assignments that contain copied material will be given zero marks.

ASSESMENT

The assignment consists of two separate parts: section 1: Object-Relational Design and

Implementation and section 2: Database Queries. Note that each part of the assignment uses a different database; section 1 uses the Online Music Database (OMDB), and section 2 uses the Movie Database (MDB). Assessment of the assignment will be based on correctness, completeness and conciseness of your design and program code. An important objective of the assignment is to demonstrate your ability to communicate in the form of a structured report. The assignment report should include the following sections:

Section 1: OMDB Object-Relational Design and Implementation (25 marks)

Section 2: MDB Database Queries (25 marks)
Appendix containing the OMDB script created in section 1.4

Poor quality of the report will result in a loss of marks.

1

1. OMDB OBJECT-RELATIONAL DESIGN AND IMPLEMENTATION

This section involves design of an Online Music Database (OMDB). OMDB maintains information about music albums available for purchase from an online music store. The database supports a website that allows users to query this information and search for albums (visit http://www.amazon.com/music-rock-classical-pop-jazz/b?node=5174 for an illustration of a similar website). Albums are available in three different formats: CD (audio CD), vinyl (vinyl record) and MP3 (MP3 download). The price of albums varies depending on the format, (vinyl records are typically more expensive than CDs of the same album). New albums are generally more expensive than used items. In the case of MP3 albums, individual tracks are priced separately and are available separately for download.

OMDB Information Requirements

Each Album is described by the following attributes:

Title: album title, e.g. “La Traviata”
Playtime: duration in minutes and seconds on a specific format (e.g. CD)
Genre: genre, e.g. classical, jazz, blues, rock, etc. (only one genre is recorded)
Format: album format: CD, vinyl, cassette or MP3
ReleaseDate: date of release of the album in a specific format (i.e. CD, vinyl or MP3)
Artists: Name and DOB (date of birth) of the main artists and their role (i.e. composer,
conductor, performer, singer, musician, etc.). Assume that an artist can only have a
Price: single role on a given album.
price of the album on a specific format (e.g. CD)
Used flag indicating if the album is new or used (does not apply to MP3 format)
Tracks: each album contains a number of tracks that contain songs or compositions; each
track is described by: TrackTitle, TrackDuration and in the case of MP3
TrackPrice.

Note that albums with the same title can be released (or re-released) on different dates and in different formats, and can have different artists involved.

1.1 Entity-Relationship Model (10 marks)

Draw an Entity-Relationship Diagram (ERD) to represent the data requirements of the OMDB database. The ERD should show all entities, relationships, attributes, identifiers, and cardinalities. Make use of sub-typing where appropriate. Use the names indicated in bold in the above description for name of entities, relationships and attributes.

Draw the OMDB ERD diagram (Logical Model) using the Oracle Data Modeler. Ensure that your ERD uses the Barker notations; this can be specified in Preferences; Data Modeler; Diagram;

Logical Model. You can download the Oracle Data Modeler from:

http://www.oracle.com/technetwork/developer-tools/datamodeler/overview/index.html. Data Modeler User’s Guide is available on: https://docs.oracle.com/en/database/oracle/sql-developer-data-modeler/18.2/dmdug/oracle-sql-developer-data-modeler-users-guide.pdf

The deliverable for this section should be the OMDB ERD diagram.

1.2 Relational Model (5 marks)

Convert the OMDB logical model into a set of corresponding tables (Engineer to Relational Model). Draw the table diagram (Relational Model) and ensure that the resulting tables are in BCNF.

The deliverable for this section should be the OMDB table diagram.

2
1.3 Object-Relational Model (5 marks)

The task in this section is to convert the OMDB Relational Model in section 1.2 into an Object-Relational design. Define UDTs (User-Defined Types) and the corresponding typed tables for the OMDB database. Follow the recommendations below:

a) make all tables typed

b) eliminate unnecessary keys, i.e. keys that are not meaningful to users
c) replace all foreign keys with references

d) use collections where appropriate, (e.g. nested tables to implement compositions)

e) use subtyping, if appropriate

Consider the implications of your design on data redundancy and complexity of the solution and justify your design decisions. Document your design by including all type and table definitions

in this section.

The deliverables for this section should be a set of type and table creation statement, and justifications for your design decisions.

1.4 Object-Relational Implementation (5 marks)

Create SQL script containing the above type and table definitions and INSERT statements to populate the OMDB database. Ensure that each table contains at least three rows. Populate the tables with sample data using INSERT statements. Ensure that the script executes without errors and include the script and the results of its execution in the appendix showing all DDL and

DML statements.

The deliverables for this section should be an appendix containing the OMDB SQL script and the results of its execution.

Leave a Reply