Convert Data Models to Database Design (20 pts) Note: This assignment can optionally be completed in teams or pairs. If you choose to submit the assignment this way, you must indicate clearly on your submission all of the people who worked together. (On other assignments where I dont indicate a note such as this, I expect your work to be individual.) Never represent others work as solely your own. Everyone must still submit a copy on TITANium. Using Microsoft Visio and Microsoft Excel, create an E-R diagram for the following scenario. Then, convert the abstract E-R Diagram into a database design with more details.

You are creating a database for a small community college. The college is so new and small that everything is housed in one building and there are no sections of classes just unique classes that are only offered once per semester. Faculty members cannot be part of multiple departments. Classes are listed by department name and class number (e.g., ISDS 402). Students, of course, can enroll in multiple classes. You need to be able to store the following information: student names; student contact info; professor names; professor office room numbers; class times; classroom numbers; final letter grade for students; department names; department office room numbers. Again, dont worry about buildings assume everything is in one building. Also, for this assignment, dont worry about semesters or repeating classes over time. Lets keep it simple while we master our skills! Your answer should include the following:

A modified E-R diagram reflecting primary keys, foreign keys, and intersection tables (Visio) o Note: I call it modified because in pure E-R diagrams you dont include FKs and intersection tables

Table giving details about column properties (Excel) o Note: Use the given Excel file, which has been started for you, and finish filling it in

o Note: Use data types specific to SQL Server (see textbook if needed) The important thing is that your documentation should reflect all tables, their primary and (where applicable) foreign keys, the relationships between them, including cardinalities, and the following properties for attributes: data type, required/optional, defaults values, and any constraints. Grading Rubric:

6 points: All necessary tables are shown with primary and foreign keys documented.

8 points: All relationships are correctly implemented in the database design (i.e., through foreign keys, intersection tables, required or optional fields, etc.)

6 points: All attributes data types and required/optional status are correctly documented (some have definite correct answers; for other attributes, you can make an assumption; ask if you have any questions about a particular attribute). Any other necessary constraints or default values should be listed as well, though most attributes wont have these in your answer. Submission: Save your Visio file and your Excel file and upload both to TITANium.

Table Default Value (if none, Key? (Primary, Foreign) (lf neither, leave blank) Required leave blank) Remarks or other constraints (if none, leave blank) Column NameData Type 3 Class Yes Int Prima ClassID RoomNumber ClassTime 10 12 13 15 16 17 18 19 20 21