Question

Help with database creation - create a visio ER diagram of the scenerio below.. thanks

The registrar's office at Weber State University wants you to create a new database to support an future application that will help their department better keep track of the scheduled classes offered each semester, including the specific course sections and lecturers appearing in the schedule, and the students registering for courses according to the published schedule. Courses (think course catalog) may or may not be scheduled every semester, and this is documented in a schedule of classes being offered each semester, which also includes the lecturers assigned to each scheduled section of a course. Students register for courses according to the schedule of classes Behind the scenes, Users (students, lecturers, and other college staff) must login to the application to gain access, and the application must keep track of user logins/logouts history. Of course, users must have different levels of access, which will determine their access to different parts of the application This is exactly how we do it now at Weber State, so try to think from a Registration, Faculty, and Student data storage/retrieval perspective. What do each of these people need to read (retrieve) write (store) to the database (regardless of application interface)? Here are some business constraints to consider: A User can create many Users Each User must be created by only one [other] User A User can have many LogHistoryEntries Each LogHistoryEntry must be tied to only one User An Access Level can be designated to many Users Each User must be designated by only one AccessLevel A Student must be assigned to only one User [login] Each User [login] can be assigned to only one Student A Lecturer must be assigned to only one User [login] Each User [login] can be assigned to only one Lecturer A Course can be the prerequisite for many [other] Courses Each Course can have a prerequisites many [other] Courses A Lecturer can lecture many Courses Each Course can be lectured by many Lecturers A Lecturer can lecture many ScheuduleOfClasses Each ScheduledClass must be lectured by only one Lecturer A Course can be scheduled in many ScheduleOfClasses Each ScheduledClass must be scheduled for only one Course A Semester can be created for many ScheduleOfClasses Each ScheduledClass must be created for only one Semester A Student can register for many ScheduleOfClasses Each ScheduledClass can be registered for by many Students 1) Produce A SINGLE Visio (or similar electronic diagramming tool) document containing the Logical ER Diagram for the scenario above. Also, save the file in .pdf or jpg format. No hand drawn this time. 2) Produce A SINGLE Spreadsheet document (using tabs) containing proof of normalization showing at least 4 rows of 3rd Normal Form sample data for each of the created tables above. 3) Submit one ZIPPED file containing 3 items the raw diagram (.vsd or other diagramming tool format), and an exported .pdf copy for viewing, plus the tabbed spreadsheet (.xls) file containing proof of normalization with sample data.

Answer 1

Here, I have following ER diagram as shown below, A table is in a 3NF when all non-primary fields are dependent on the primary key and the dependency of these non-primary fields is between the data Student File Student ID string primary key Name string Address String Course ID String Foreign Key \r\n Faculty File Course ID String Primarykey Semester String Catalog String Schedule Class String Lecture String Registration File Log ID String Primary key Password String Name String Access level String History File Log ID String Primary key Password String Log history String