INFO210: Spring 2001

Assignment 2

 

Submit via EMAIL. See below for submission guidelines.

Due before class on Wed, May 23, 2001

 

Carefully study the attached ERM. (To print it from the browser, use the landscape option on your page setup).

 

It shows some of the operations of the Wonder University department of Business. The department wishes to integrate information on its students and other contacts into a single database. In the very simplified form shown here, the database is centered on people. This part of the system is basically a name and address book. There are two kinds of people – students and business contacts. Different information is stored on each. Business contacts all work for companies. In addition, the system supports lists of people. A list is basically any subset of the people in the database. Examples of lists might include “Class of 03 Corporate Mentors” or “Honors Students”.

 

 

(for a slightly clearer, stand-alone image of the diagram, click here)

 

1) Translate the ERM into a relational schema. (See my handy hints for ERM translation – now here). 30 points

Use the format shown in the text book

 

Relation_Name (Attribute_1, Attribute_2, …. Attribute_N)

Primary_Key (Key Attribute_1) <– remember, the key may be compound!

Foreign_Key(Attribute_In_This_Relation) references Other_Relation.Attribute_In_Other_Table

 

Some discretion is involved in the translation method used with the subclass-superclass. Any reasonable solution will receive credit.

 

2) Provide the Create Table statements necessary to implement these relations as tables in Oracle. 25 points

 

Make reasonable assumptions about the data types to use for each field.

Include constraints. At a minimum, make sure you show primary keys and other fields that cannot be null.

 

Extra Credit: Implement other constraints such as foreign keys with cascading deletes, or check constrains to ensure sensible values.

 

3) Create sample data to populate each of these tables. Supply this as a series of insert statements. 15 points

 

Provide at least three contacts and two students. Set up at least two lists, with at least two people in each. Have at least two companies, make sure that more than one contact works for at least one of them.

 

Make test data that will work well with the queries below. This means you should include at least one contact and one student living in New York state, at least one student with the major ‘PHYS’ and at least one person whose name begins with ‘P’.

 

4) Write SQL statements to perform the following 6 points each

 

a) Show the name, city and telephone of everybody in the database, ordering by name in a sensible manner.

b) List the names and majors of all the students on a particular list.

c) Delete everybody whose last name begins with the letter ‘P’.

d) Change all the students whose major is ‘PHYS’ to have the major ‘ENGL’.

e) Show the last names and, if applicable, the company names, of all the people in New York state. )For full credit, make sure that even students are included – but with the company name blank as this will not apply).

 

How to submit:

Send me an email. In the body of the email, nicely formatted (with question numbers and line breaks), include the answers to each of these questions.

 

As an attachment, send a plain text file holding the results of running your SQL. Do this by enabling logging in your telnet window, or by setting a very big buffer and then pasting the results. Then run each of the commands in parts 2-4. Include your commands and the results. Do not try and fake this to make it look like it works – serious penalties will result.

 

Reminder: This is NOT a group project. It is fine to discuss in general terms the techniques you will need, but the regular Drexel plagiarism policy applies. Excessive similarities between your answers and those of those students will result in both of you receiving the appropriate penalties.

 

Extra Credit:

 

Write queries to

a) Delete from the database all the students on a particular list.

b) List all students, together with the number of days that have elapsed since they enrolled.

c) Count how many students have not yet declared a major. (Assume major_1 will be null in this case)

 

d) (a big section – lots of credit here, but don’t try it unless you’re feeling very keen indeed)

Expand the database to include support for meetings. For each meeting, store the name, date and location. There is a m:n relationship called “invited” between person and meeting, so you will need to implement this as an additional table called “invitation”. Each invitation will store the date on which it was sent, and additional information such as whether or not the person is coming, the date on which a reply was received and whether a reminder has been sent.

 

Make sample data for at least two meetings. Write queries to insert new invitations, and to update them once a reply is received. Write queries to show all attendees for a meeting, and to count how many people have so far replied.