Orinoco is a medium sized record producer offering music on CDs, Tape, and Vinyl. They have just brought a number of smaller recording companies and are rapidly expanding. There is a great deal of information in the brochures that Orinoco produces along with an accompanying price guide. Some of the information is on computer but this is somewhat cumbersome as it is in separate systems that are incompatible, incomplete and difficult to use (due to the recent takeovers). It is particularly difficult to find music that matches a customers specific requests unless the brochure is browsed carefully. Orinoco has decided to computerize the information in their brochure, to include purchase information, and to sell directly over the web. They need to make it easier to: Gather specific information from customers and answer questions about products. Check the availability of specific products. Give costs of products. Keep information about artists, albums, and tracks. Keep information about the back catalogues of the companies recently taken over. Keep contract information about artist signings etc. For simplicity, you may assume that the system is stand-alone and does not have to integrate with any other. Note that many other simplifying assumptions will be made (e.g., that price information is fixed and is not flexible, and there are no sale items etc.). You should be sure to solve the problems as specified not as you imagine they would be in the real world. Each Artist has an artistic name a musical genre and is managed by a manager who can have a series of contact telephone numbers. Artists also have a contract with Orinoco, which has a date from and to, and the duration of the contract in days. An Artist records a number of master tracks which have IDs a working title and duration; tracks are edited by a sound engineer, who has a name and ID. A finished track originates from a master track and has a version number (a finished track may have a number of different versions) a released title and a final duration. Albums group finished tracks into a sequence or play list / order. Albums have a title, are created by an artist, and can be distributed as a CD, on Vinyl, or as a Tape Cassette (denoted by c, v, and t respectively).

What you need to do this exercise A Dia file has been created for you to extend and can be found on Blackboard Basis.dia represents a part-completed EER schema which, models some of the information implemented in the system, as a starting point for this exercise.

1. Understanding the EER schema for the Orinoco system. To demonstrate that you understand the information represented by the schema, explain using MANAGER, CONTRACTINFO, ARTIST, MASTER TRACK, FINISHED TRACK, ALBUM, and SOUND ENGINEER as examples: a) attributes, entities and relationships b) cardinality & participation constraints on relationships You dont need to explain the entities etc BUT you should explain question a and b using the schema you have been given to more easily explain your answers. Dont write more than half a page to answer question 1.

2. Creating and Extending Entity Relationship Diagrams. To demonstrate you can create entity relationship diagrams extend the EER as described in Basis.dia by modelling new requirements as follows: a) The system has an online catalogue, which stores a reference to an album. An album can have a number of entries in the catalogue determined by the release date of the album (to account for re-releases). The catalogue also stores the price of each entry and the number in stock (available). b) A buyer can place an order. We use the buyers code to uniquely identify the buyer with the order and so an entity for the buyer needs to be created (we also store their name). c) A buyer can place any number of orders, each of which has an order number and a dispatch date. An order also composed of separate parts each of which referrers to a catalogue entry. d) Create subclasses to extend Artist. Two different types of Artist exist: a Solo artist and a Group artist. A solo artist can be a member of more than one group and group is made up of one or more solo artists. e) Extend Album thus: an album comes in one of three types Vinyl, Tape, and CD discriminated by v, t, and c respectively. A vinyl album can come in a number of colors, a CD album can have a number of different pc extras, and a tape can have only one label type. f) Now explain the category relationships: supertypes and subtypes that you have just created. g) Explain the relationship CreatedBy. h) Explain the relationship GroupedOn. i) Is it possible for many artists to appear on one album?

Report You must hand in the following: 1. Explanation of what the given EER diagram means 2. Completion of the EER as per the specifications in the questions 3. Good Report Style

basis.dia * + 110, 15, 120, 11, 125, 10, name date_from contract ID telephone date_to Contractinfo duration Manager HasContract Managed By artistic name 110 working_title genre Artist duration 3 DOSIS.Uld 130 1 track ID duration RecordedBy Master Track EditedBy OriginatesFrom> ozl. IIIIIIIIII Finished Track Groupedon version sequence final_duration basis.dia * 25 rack_ID duration 3 name | SITEL | | Master Track | EditedBy - Sound Engineer CreatedBy | | title OriginatesFrom> album_ID Finished Track Groupedon Album sequence nal_duration asis.dia * +-25 Contractinfo A duration _HasContract> 1 Sound Engineer CreatedBy basis.dia * + 110, 15, 120, 11, 125, 10, name date_from contract ID telephone date_to Contractinfo duration Manager HasContract Managed By artistic name 110 working_title genre Artist duration 3 DOSIS.Uld 130 1 track ID duration RecordedBy Master Track EditedBy OriginatesFrom> ozl. IIIIIIIIII Finished Track Groupedon version sequence final_duration basis.dia * 25 rack_ID duration 3 name | SITEL | | Master Track | EditedBy - Sound Engineer CreatedBy | | title OriginatesFrom> album_ID Finished Track Groupedon Album sequence nal_duration asis.dia * +-25 Contractinfo A duration _HasContract> 1 Sound Engineer CreatedBy