Effective Database Design for College and Trip Management
School
Niagara University**We aren't endorsed by this school
Course
CIS 270
Subject
Information Systems
Date
Dec 11, 2024
Pages
4
Uploaded by BrigadierIron89507
Louange MizeroValerie DevineData ManagementASSIGNMENT 6 - Case studyPart 1: College Database1.Departments Table:Department (DepartmentNum, DepartmentName)DepartmentNumb → DepartmentName○SK DepartmentName2.Advisors Table:Advisor(AdvisorID, AdvisorName, DepartmentNum)AdvisorID → AdvisorName, DepartmentNum○FK DepartmentNumber → Department3.Courses Table:Course (CourseCode, CourseDescription)CourseCode → CourseDescription4.Students Table:Student(StudentID, StudentName,(CourseCode, CourseDescription), AdvisorID, AdvisorName)StudnetID → StudnetNameCourseCode → CourseDescriptionAdvisorID → AdvisorName
StudentID → CourseCodeStudent(StudenttID , StudentName, AdvisorNum)Course(CourseCode, CourseDescription)Advisor(AdvisorID, AdvisorName)StudentCourse(StudentID, CourseCode)FK AdvisorID → AdvisorFK AdvisorNum → AdvisorFK CourseCode → CourseFK CourseDescription → CourseFK StudentID → StudentFK CourCode → Course
Part 2: Design Database1.Guide(GuideNum, LastName, FirstName, address, CityState, PostalCode, telephoneNum,DateHired)GuideNum →LastName, FirstName, address, CityState, PostalCode, telephoneNum, DateHired.2.Trip (TripID,TripName, StartLocation, State, Distance, MaximumGroupSize, Type, Season.(GuideNum, LastName, FirstName))TripID →TripName, StartLocation, State, Distance, MaximumGroupSize, Type, Season.GuideNum → LastName, FirstNameTrip (TripID,TripName, StartLocation, State, Distance, MaximumGroupSize, Type, Season)Guide (GuideNum, LastName, FirstName)TripGude (TripID, GuideNum)FK GuideNum → GuideFK TripID → Trip3.Participant(ParticipantNumber , FirstName, LastName, Address, City, State, PostalCode,TelephoneNum.(TripID))ParticipantNumber → FirstName, LastName, Address, City, State, PostalCode, TelephoneNumTripID → → ParticipantNumParticipants (ParticipantNumb, FirstName, LastName, Address, City, State, PostalCode,TelephoneNum)ParticipantTrip( TripID, ParticipantNumb)FK TripID → TripFK ParticipantNum → Participant