SQL Queries for Course and Student Data
Added on 2020-02-24
11 Pages1327 Words47 Views
|
|
|
Running head: DATABASE IMPLEMENATIONDatabase ImplementationName of the Student:Name of the University:Author Note
1DATABASE IMPLEMENATIONTable of ContentsPart 1: ER Model Implementation...................................................................................................2ER model.....................................................................................................................................2List of Entities and Attributes......................................................................................................2List of Business Rules.................................................................................................................3Assumptions and Justification.....................................................................................................3Part 2: Relational Database Implementation...................................................................................4Functional Dependencies.............................................................................................................43NF resolutions............................................................................................................................5Data Integrity...............................................................................................................................5Tables and Database Structure.....................................................................................................6SQL Queries................................................................................................................................8Bibliography..................................................................................................................................10
2DATABASE IMPLEMENATIONPart 1: ER Model ImplementationER modelCoursesStudentInvoiceStaffInstrumentsCourseIdPKCourseNameCostStudentIdPKFamilyNameCourseIdInvoiceidPKStudentIdStaffIdstaffIdPKStaffNameSalaryDurationCourseIdStatementDatePaymentDateEnrolled inteachesgeneratesPaymentInstrument_idPKInstrument_namemanufacturerQuantityStockInstrumentIdUsesAgeGivenNameSchedulePaymentStatusStatementTotalFigure 1: ER DiagramSource: (created by Author)List of Entities and AttributesEntitiesAttributesStudentsStudentId (primary key), FamilyName,GivenName, CourseId, AgeStaffsStaffId (primary key), StaffName, Salary,CourseId, InstrumentIdCoursesCourseId (primary key), CourseName, Cost,Duration, ScheduleInvoiceInvoiceId (primary key), StaffId, StudentId,StaementDate, PaymentDate, PaymentStatus,StatementTotalInstrumentsInstrucmentId (primary key),
3DATABASE IMPLEMENATIONInstrumentName, manufacturer, Quantity,StockList of Business RulesThe list of Business Rules for the database are:Students are enrolled for the courses.The Staffs teaches a particular course.A staff can teach more than one course.The Staffs make use of one or more instruments.The staffs generate the invoice for the students.The students make the payment according to the invoice generated by the staffs.Assumptions and JustificationFor the implementation of the system the following assumptions have been taken intoconsideration.It can be assumed that the staffs make use of the equipment and the students bring theirown equipment and use them. Hence, only the relationship in between the staffs and theequipment are maintained. This makes the database easier and reduces the furthercomplexities of the database.In addition to this it is also assumed that a student can enroll himself into a number ofcourses and also the vice versa is also possible that is a number of student is enrolled intoa single course. Hence, a many to many relationships in between the students and the
End of preview
Want to access all the pages? Upload your documents or become a member.
Related Documents