Problem 1:

A local recording studio wishes to keep track of all information about the CD's they release in a relational database. The following describes the situation.

A CD has a title, artist, release date, label (the company that released the CD), producer (the person who produced the CD) and contains a collection of songs. The artist is the musician or band whose music appears on the CD. If it is a compilation CD with many artists, the name "Various Artists" is used for the artist. Here are some examples of CD title and artist combinations:

Title: Goats Head Soup Artist: The Rolling Stones
Title: Bring The Family Artist: John Hiatt
Title: Staying Alive Artist: Various Artists
Title: Toy Story 2 Soundtrack Artist: Various Artists

Each CD has many songs on it (usually up to about 20). A song has a song title, composer (the person, or persons who wrote the song), a publishing company, and lyrics. The lyrics are the words of the song. A particular song can appear on several different CD's and be performed by different musicians on each CD. For example the song "Thing Called Love" written by John Hiatt appear on his CD with his band and the song also appears on a Bonnie Raitt's CD performed by her band.

To record a song for a CD musicians record tracks. For example the drummer will record a drum track, the bass player will record a bass track, and there would be tracks for vocals, guitar, keyboards, background vocals etc. The database should keep a record of these tracks including the track name, the musical instrument, the performer etc.

A song for a CD is created by assembling all the individual song tracks. The studio wants to use the database to find all the appropriate song tracks when assembling the CD and its individual songs. They also want to use the database to list the CD credits when the CD booklet is produced. The CD booklet will include for each song, the song's name and lyrics, the author, publisher, and all the musicians that played on the song and the instrument they played. For the purpose of this database the lyrics of a song can just be treated as one long string attribute.

Problem 2:

In assignment #1 you provided a script to populate a table of fake book songs. It was intended to provide an indexing database for music students. Now we want to model a database that will make use of this information and also the additional information needed to support the intended application as described below.

Here are more details.

Musicians use fake book charts to play and improvise from. We want to create a database that will use the indexing information provided in assignment #1 an support the following.

The users (musicians) will upload their own copies of .pdf books to the application. The books supported will be those that appear with the indexing data (books in the bookcodes table from assignment #1). When a musician has uploaded a book they will have the right to be shown pages of that book using the indexing data in the database. The musicians will only be allowed to see the books they have uploaded, but they can search all of the indexing data. For copyright reasons they will not be allowed to see contents from other books. If two musicians upload the same book then only one copy of the book needs to be stored by the application.

The indexing information should provide the books and page numbers for the various songs. Each books should also have some kind of offset information to account for introductory pages. That is, if the song is indexed to be on page 1 but that is the 10th page of the .pdf book then an offset should be stored in the database to account for this.The database should store information about the individual books which includes their book code (unique), title, publisher, and date of publication.

The database should support a collection of users. Users have a name, email address, userid, password. The database must keep track of which books which users are allowed to access.

Design an E-R model for this database. You will have to make decisions about attributes and keys. If you don't think it is clear what your attributes mean then provide some notes and assumptions to go with your design. It is expected that this question will require classroom discussion to clarify what is required. Make sure to ask lots of questions.

Problem 3:

A telephone switch is a computer that has lines and trunks connected to it and can make internal connections between two lines, a line and a trunk, or two trunks. A line connects a telephone to the switch; a trunk connects two switches to each other. The interconnected switches form a telephone network. A line can only support, or carry, one conversation, but a North American T1 trunk can carry 24 conversations (it multiplexes 24 conversations on a single pair of wires. The different conversations of a trunck are identified by channels. A T1 trunk has 24 logical channels (numbered 0 to 23).

Lines and trunks both connect to the computer using an interface card. Each interface has a unique logical identifier, called portID. A switch can have up to 60,000 lines and up to 5000 trunks connected to it.

When you rent a phone line from the service provider it is assigned a directory number (e.g. 613 737-2443). Your customer details like name, address, emial, is stored in the database.

In North America telephone directory numbers are 10 digits long. In a directory number, like 613 737-2443, the first three digits (613) are called the area code and traditionally designates some part of a province or state. Office codes can be reused in different area codes. For example 613 737-2443 could be a number in Ottawa whereas 416 737-2443 might be a number in Toronto.

To make a call you go off-hook on your phone line and dial the number of the line you wish to reach. If the call is to another phone on the same switch the two lines are interconnected by the switch software. If you dialed the number of a phone on another switch, a trunk must be selected which will carry the conversation to another switch. The process of choosing the appropriate trunk is called routing and is described below.

Trunk routing works like this. Trunks are assigned routing area and routing office codes. A trunk with a routing area = 613 and routing office = 232 is willing to handle any call going to a phone number 613 232-XXXX. A trunk with routing area = 613 and routing office = 000 will handle any call going to the 613 area code. A trunk with routing area = 000 and routing office = 000 will handle a call to any number.

So to route a call the switch must consult the database and determine if the dialed number is among those of its lines and if so the call will be connected. If the dialed number is not one of the lines of the switch the software will use the database to select an appropriate set of trunks and choose one of the available ones. The prefered trunk would be one that has a matching routing area and routing office as the dialed digits, the next preferred trunk would match the routing area and with routing office = 000 and finally the last resort trunk would be one with routing area = 000 and routing office = 000. I any case, a trunk can only be used if it still has an idle channel available.

The person, or line, who originates the call, and dials the digits is called the originator. The line or trunk to which the call gets connected is called the terminator. Trunks can also be originators (in this case the dialed digits are coming from another switch from the other end of the trunk).

If the called line you are trying to reach is already busy on a call or no idle channels on the desired trunks can be found the caller receives a busy treatment (a tone or announcement). There are other kinds of treatments for an incorrectly dialed number, or service not being available etc. (For a call which uses a trunk both the called digits and the callers digits are sent over the trunk to the next switch so the next switch over can do routing using its own database.) So treatments are recorded messages that can be played over the phone line for the originator to hear. Treatments are implemented by having their own portID so a call can be connected to a treatment just like it can be connected to a line or a trunk channel.

The switch developer wants to put all data need to support making calls in a relational database. The data should provide information necessary to identify lines and trunks, determine if they are busy or have idle capacity, have a record of all calls currently in progress, have the data necessary to make routing decisions and select approriate trunks based on the origintors dialed digits. The customer wants to be able to see all calls currently in progress including who the originator and terminators are (their portID) and channels being used. The customer also may want to query who the actual customer is: their name, address, email etc.

This question contains lots of technical information and industry jargon. It is expected that you ask lots of questions in class to help clarify things.

Problem 4:
This course has a project component. That is, an ongoing design of your own choosing. You are going to build your own database about something that interests you. In assignment #1 you provided a brief scenario and proposal. Now you want to create the first model of your database. Later in the course you will build this with SQLite and populate it with data.

For this assignment you must re-write the scenario to describe what your database will be about, and then come up with the proposed E-R diagram and initial table schema as for the previous practice questions. You wrote a scenario or domain description in the previous assignment but we want you to repeat it here. This will give you a chance to make modifications and to make sure the information is here for whoever is evaluating your E-R model. (If it has not changed from assignment #1 then just copy and paste it here again.