Dean of Admission at DeAnza College has hired you to do the database design for a new registration system. Keeping in the mind the following requirements, design a Database Schema. 1. College has several departments 2. Department offer several courses 3. Courses have sections. 4. College has locations (Room Sem 5 etc.) 5. Students register for a section from a catalog (which is composed of Departments, Courses, Sections, Location.) 6. Students have to pay for the course they take. You may want to think about resident, non resident. 7. Their status is updated based on their registration. A document to help you remind the symbols for DB Schema is posted in the notes area for Session 1 - called "Introduction to RDBMS.doc".

Entity/Attributes [PARENT] User - id(pk), Firstname, Lastname, Address, Email, Telno, ResidentId, Type [PARENT] Location - id(pk), Location Name [PARENT] Payment - id(pk), PaymentName //Payment - visa/mc/cash - optional [PARENT] Resident - id(pk), ResiName, Feeschedule //Resident/Non-Resident [PARENT] College id(pk),Name, Address, Telno [CHILD of College] Department - id(pk), Name, Locid, Collegeid(fk) [CHILD of Department] Course - id(pk), CourseNo, CourseName, Prereq, Deptid(fk) [CHILD of Course, User(instructor) and Location] Section - id(pk), SectionNo, Courseid(fk), Locationid(fk), starttime, endtime, startdate, enddate, userid(fk)

[Child of User, Section, Payment] StudentRegistration - id(pk), StudentID(fk), SectionId(fk)_, FeeStatus, Paymentid(fk)

//student-section Catalog - view (query - Department, Course, Section, Location)

Relationships 1. College(parent) can have zero or more department(child) 2. Department(parent) has zero or more courses //Students complete the listing of the relationships

Crow Notation Representation //Completed by Students crow notation practice.ppt is just a example and not a complete representation of the schema. You are supposed to create this document and the .ppt file only provides a starting point.

How to design the database schema with these requirements and information?