ERD to/from RDB Schema

(20 points with 5 points extra credit)

Setup

1. Create a top-level directory in your repository named exactly ERD-RDB.
2. Place the files named in the parts below into this directory.
3. Add / commit / push these files to your pushbox by the due date.

Part 1 - ERD to RDB Schema - Library.sql

The following ERD models a simple library system.

• The library holds copies of books; each copy is of a single book, and a given book has one or more copies (for instance, there would be more copies of Lord of the Rings than Everyday Thermodynamics).
• Library patrons may check out copies of books for a limited time. Each copy is checked out to at most one patron, and each patron has zero or more books checked out.
• Each book has an author, and an author will have have written zero or more books.

Edit Library.sql and add the necessary SQL commands to create four tables corresponding in the ERD. You must include appropriate primary key declarations as well as foreign key references. In addition, when this file is .read into sqlite3 it must create the four tables without error - that means you must pay attention to the order in which the tables are created.

(10 points: 3 for creating tables w/o error, 5 for correctness and completeness, 2 for clarity and readability of the commands).

Part 2 - ERD from Specification - Enroll.pdf

Create an ERD corresponding to the specification of the simple course enrollment system given below. You do not have to identify the primary keys for the given entities, and you do not need to show foreign key attributes. You must, however, correctly show the multiplicity for each of the relationships.

You can create your ERD using any drawing program, or you may draw the program by hand and scan it in. Hand-drawn diagrams should be done in black ink and the lines must be wide enough for readability. What is more, neatness counts!

However you create the ERD, it must be converted to PDF and placed in file Enroll.pdf.

Entity Specification

There are four entities the system must model:

• Professor:
  Attributes are a unique integer id, a text name, a text department, and a text email.
• Student:
  Attributes are a unique integer id, a text name, and a text major.
• Course:
  Attributes are a unique integer id, a text department, and a text title.
• Section:
  Attributes are a unique integer id, a text room, an integer time, and an integer capacity.

Relationship Specification

Course - Section

Each course has 0 or more sections and each section is associated with exactly 1 course.

Section - Student

Each section has 0 or more students enrolled and each student is enrolled in 0 or more sections.

Professor - Section

Each professor teaches 1 or more sections and each section has exactly 1 professor.

Student-Professor

Each student has exactly 1 professor as an advisor and each professor advises 0 or more students.

(10 points: 4 for proper entity definition, 4 for proper relationship definition, 2 for clarity of organization and presentation)

Part 3 - Extra Credit - Organization.sql

Consider the following ERD showing the relationship between Persons and Organizations:

This represents a situation in which a Person can belong to 0 or more Organizations while each Organization has one or more Persons as members. Note that we cannot put a foreign key attribute in either of the entities because there is no way for a Person to "link to" a unique Organization or vice-versa.

Do the necessary research to find the standard solution to handling such "many-to-many" relationships. Edit Organization.sql and add SQL commands to create all the tables required to represent the ERD. Be sure to order the SQL statements so that when .read into sqlite3 no errors will be generated.

(5 points: 1 point for creating tables with no errors; 3 points for correctness and completeness; 1 point for clarity and formatting).