The university database keeps track of a university's students, student majors and minors, departments, courses, sections of courses, assigned grades, and university owned laptops assigned to students. The database designers stated the following description of the university mini-world after the requirements collection and analysis phase:
1.The university keeps track of each student's name, social security number, known addresses, birth date, sex, class (freshman, sophomore, , graduate), and degree program (A.A., A.S., B.A., B.S., Ph.D.). The university keeps track of the different department(s) that students major and minor with.
2.Each department has a name and department code both uniquely identify the department. Information about department membership in a university college is also maintained.
3.Courses are offered by departments and have a name, number, a fixed number of credit hours, and level (entry, upper-level undergrad, grad, etc.). The course number uniquely identifies each course.
4.When a section of a course is offered, the semester and year are recorded along with a section number. Section numbers are number 1, 2, 3, etc., for as many sections as are offered during the semester and year. The name of the section's instructor is also recorded.
5.An assigned grade is recorded for each student registered for a particular section of a course. Letter grades are recorded as A, B, C, D, F, W, and I. Number grades are also recorded as 4 for A, 3 for B, 2 for C, 1 for D, 0 for F, W and I.
6.Students are assigned a university owned laptop if requested by the student. Data stored on each laptop includes its unique serial number, manufacturer, type of operating system and date of the image build currently installed on the laptop.
ER Diagram for the University Mini-world will be based on the following list of: entities, attributes and relationships.
Entities and Attributes:
Relationships and Attributes:
The following is a list of sample queries that a user might make against our new database.
1.Which laptops (Serial#) are manufactured by Dell?
2.Which departments (Name and DeptCode) are in the college of Natural Science?
3.Which laptops (Serial#) are running Windows 2000?
4.How many laptops are running a Windows operating system?
5.How many departments are in the college of Education?
6.Which laptops (Serial#) have a build between 1/1/1999 and 12/31/2001 inclusive?
7.How many female and male students are there per class?
8.What is the average grade (use number grade, not letter grade) assigned during spring 2000?
9.What is the minimum letter grade assigned?
10.What is the total count of each assigned letter grade?
11.What is each student's average grade (use number grade)?
12.Which departments (Name and DeptCode) are located in Knob View Hall (KV)?
13.List the students (SSN only) who have 2 or more majors.
14.Which instructors have taught 400-level courses?
15. List the courses (CourseNum only) that are lower level and 4 credit hours.
As you can see, the number of interesting questions we can ask to this database are very numerous, and this is only a fraction.
The first thing you need to do is to construct an ER diagram that models this data. Specify cardinality and participation constraints. Then transform your ER diagram into a set of relations.
Next, you need to implement these relations using any DBMS of your choice. You must use the following data set to populate your database.
Finally, execute the above list of queries and other queries for test.
You need to hand in a hard-copy of the ER diagram, relational schemas, tables with actual records, SQL query statements, and the result from the queries.
You should use the following information to populate your database for the project.
Student
(SSN, StudentNum, Laptop-SerialNum, FName, MInit, LName, DegreeProgram, BDate, Sex, Class, Major, Minor)
(031771111, 5, 2344-9011-A, Paul, Y, Conte, B.A., 1/12/1962, M, senior, Spanish, Null)
Address: 7 East Duffy Ln, Seymour, IN, 45132
Courses Taken: CourseNum - SectionNum - Semester - Year - LetterGrade - NumGrade
E280 - 2 - SP - 01 - C - 2
H101- 1 - SP - 01 - C -2
M216 - 1 - SP - 02 - B - 3
(111286556, 8, Null, Susan, L, Morrison, B.S., 9/18/1982, F, freshman,
Elementary Ed, Null)
Address: 4th and York, Bardstown, KY, 40234
(308901643, 9, Null, Ed, C, Halman, B.A., 12/21/1948, M, senior, Null, Null)
Address: 550 10th St, New Albany, IN, 47150
(403053232, 6, Null, Carol, V, Adams, B.S., 5/17/1980, F, junior, Fine Arts, Chemistry)
Address: 177 Sunset St, Los Angeles, CA, 90788
Courses Taken: CourseNum - SectionNum - Semester - Year - LetterGrade - NumGrade
H101 - 1 - SP - 01 - A - 4
P251- 1 - SP - 01 - A - 4
(589606080, 1, 3343-1190-A, Joe, E, Holly, B.S., 10/10/1977, M, freshman,
Spanish & Computer Science, Null)
Address: 318 North St, Manassas, VA, 21331
Courses Taken: CourseNum - SectionNum - Semester - Year - LetterGrade - NumGrade
B461 - 2 - SP - 00 - A - 4
C421- 1 - SP - 00 - B -3
(589606081, 2, 2496-9958-A, Sally, K, Alberts, B.A., 3/22/1967, F, senior, Economics, Spanish)
Address: 4400 Grant Line Rd, New Albany, IN, 47150
Courses Taken: CourseNum - SectionNum - Semester - Year - LetterGrade - NumGrade
B461 - 1 - SP - 00 - B - 3
(712902314, 7, GW12900-N175, Amy, K, Palmer, B.S., 7/7/1977, F, sophomore, Elementary Ed & Secondary Ed, Marketing)
Address: A-33 Avenue NW, Texas City, TX, 76589
Courses Taken: CourseNum - SectionNum - Semester - Year - LetterGrade - NumGrade
M215 - 2 - FA - 01 - B - 3
M216- 1 - SP - 02 - A -4
Department
(Name, DeptCode, College, OfficePhone, OfficeNum)
(Spanish, 1, Humanities, 941-2281, KV001)
(Sociology, 2, Social Science, 941-2282, CV001)
(History, 3, Social Science, 941-2283, CV002)
(Computer Science, 4, Natural Science, 941-2284, PS100)
(Chemistry, 5, Natural Science, 941-2285, PS200)
(Philosophy, 6, Humanities, 941-2286, KV301)
(Fine Arts, 7, Humanities, 941-2564, KV010)
(French, 8, Humanities, 941-2281, KV001)
(Mathematics, 9, Natural Science, 941-2284, LF122)
(Economics, 10, Business, 941-2323, HH212)
(Elementary Ed, 11, Education, 941-4432, HH017)
(Secondary Ed, 12, Education, 941-4432, HH017)
(Marketing, 13, Business, 941-2323, HH212)
Course
(CourseNum, DeptCode, Name, Description, SemesterHours, Level)
(B461, 4, Database Concepts, Foundations of DB, 3, upper)
(C201, 4, Intro to Software Development, Fundamentals of Programming, 4, lower)
(C202, 4, Software Development II, Fundamentals of SE, 4, lower)
(C421, 4, Computer Organization, Hardware Concepts, 4, upper)
(E280, 10, Business Stats I, Intro to Business Statistics, 3, lower)
(E281, 10, Business Stats II, Intro to Business Statistics, 3, lower)
(F100, 8, French I, Beginning French I, 4, lower)
(F150, 8, French II, Beginning French II, 4, lower)
(H101, 3, History Today, Intro to History, 3, lower)
(M119, 9, Calculus Lite I, Intro to Calculus Lite, 3, lower)
(M120, 9, Calculus Lite II, Intro to Calculus Lite, 3, lower)
(M215, 9, Calculus I, Beginning Calculus, 5, lower)
(M216, 9, Calculus II, Calculus, 5, lower)
(M301, 13, Marketing, Intro to Marketing, 3, upper)
(M303, 9, Linear Algebra, Linear Algebra, 3, upper)
(M311, 9, Calculus III, Advanced Calculus, 3, upper)
(M360, 9, Probability, Probability, 3, upper)
(M366, 9, Statistics, Statistics, 3, upper)
(P251, 6, Symbolic Logic I, Prop. & 1st Order Logic, 3, lower)
(S100, 1, Spanish I, Beginning Spanish I, 4, lower)
(S150, 1, Spanish II, Beginning Spanish II, 4, lower)
Section
(CourseNum, SectionNum, Semester, Year, Instructor)
(B461, 1, SP, 00, jholly)
(B461, 2, SP, 00, jholly)
(C202, 1, SP, 00, gmanwani)
(C421, 1, SP, 00, rwisman)
(E280, 1, SP, 01, fwadsworth)
(E280, 2, SP, 01, fwadsworth)
(H101, 1, SP, 01, jfindling)
(M215, 1, FA, 01, clang)
(M215, 2, FA, 01, clang)
(M216, 1, FA, 01, jwoeppel)
(M216, 1, SP, 02, dshi)
(M303, 1, FA, 01, clang)
(P251, 1, SP, 01, brumsey)
Laptop
(SerialNum, Manufacturer, OpSys, DateBuild)
(2344-9011-A, Dell, Win2000, 2/2/2001)
(2496-9958-A, Dell, Win98, 3/1/1999)
(3343-1190-A, Dell, Win98, 4/1/1999)
(3422-99-9110, Dell, Win2000, 6/7/2001)
(A2002-CA-1211, Apple, MacOS 9, 12/1/1998)
(GW12900-N175, Gateway, Win95, 11/1/1997)
(GW34222-N200, Gateway, WinXP, 1/5/2002)