95%(21)20 out of 21 people found this document helpful
This preview shows page 1 - 4 out of 11 pages.
MS SQL Server 2014IT 204: Final Project MS SQL Server 2014 for Birchwood Lane Schools1
MS SQL Server 2014In order to properly analyze the case, we need to determine what the complete and valid data requirements are for Birchwood Lane Schools, by figuring out what data is needed and why.This can accomplished by looking at what each entities’ specific needs are and what purpose each need serves to the overall goals of the school. The faculty and instructors will need to locate the following information on the database and will use the following information to keep track of each class every enrolled student has taken; student’s final grade, overall GPA, and how many classes are remaining in their degree plan. Student data will include: full name, phone number, email address, birthdate, enrollment date, graduation date, courses taken, courses currently enrolled. The faculty will want to use the instructor’s information located on the new database (listed below) to keep track of what courses they have taught and which courses they are currently assigned to. This will allow the faculty to create schedules for terms more efficiently and avoid scheduling issues with instructors: Instructor’s data (full name, phone number, email address, birthday, hire date, courses taught, and courses currently assigned).The student’s and their parents will want to know how they can access their own data, (full name, phone number, email address, birthdate, enrollment date, graduation date, courses taken, courses currently enrolled). This will allow both the students and parents to verify that the information in the database is accurate and up-to-date and will also help the student and parents when determining what classes the student should take each semester, in order to avoid delaying their graduation date.Additional information such as a student’s history of academic or disciplinary issues should be included for the school’s reference, in case of future occurrences, or if the student has a learning or physical disability it should be annotated in the school’s records on the database. 2
MS SQL Server 2014The school can use this information to offer the student special accommodations in their classes and while attending the school. Relational Database ModelSince SQL Server is a relational database management system (RDBMS), I suggest usingthe Entity-Relationship (ER) model. While developing this model, entities, relationships, and attributes will be identified and will in turn, normalize the data. The ER model includes:Entities to track: Students, Instructors and Courses (both taught and enrolled in)Attributes to track: Students: full name, full address, phone number, email, birthdate enrollment date, graduation date, courses taken, courses currently enrolled. Instructors: Course data: course name, course start date, course end date, course category, instructor, studentsCardinality: Track information by last name & course number (Junic, Vrbsky & Nestrov, 2014).