I. I. Instructions: In this file, you will find a list of database project ideas
ID: 3741735 • Letter: I
Question
I. I.
Instructions:
In this file, you will find a list of database project ideas. You must choose one of them to design and implement.
You can work on this project as a group (maximum 3 students) or individually. You have to send the names of group members to your instructor by the end of week 7.
This project worth 8 marks, will be distributed as in the following:
Design the database, following an ER model. (2 marks)
Normalize the tables ( each table should be in 3NF at least) (2 marks)
Use MySQL or any other database to create the normalized tables and
populate your tables with at least 5 rows. (2 marks)
Execute the sample requested queries. (2 marks)
Each student has to submit one report about his/her chosen Project via the Blackboard (Email submission will not be accepted which will be awarded ZERO marks) containing the following:
ER Diagram.
All schemas before and after normalization.
All SQL statements of:
creating tables
inserting data in tables
queries.
Screenshots from MySQL (or any other software you use) of all the tables after population and queries results.
You are advised to make your work clear and well-presented, marks may be reduced for poor presentation. This includes filling your information on the cover page.
You MUST show all your work, and text must not be converted into an image, unless specified otherwise by the question.
Late submission will result in ZERO marks being awarded.
The work should be your own, copying from students or other resources will result in ZERO marks.
Use Times New Roman font for all your answers.
Project 3
Grade Book Database
This is supposed to develop a database to manage a grade book. Your product should contain information about courses, students, grades for homework, quizzes, midterm, ?nal, and various grades.
Each course has id, title, credit hour.
You should store an id, name, email for each student.
Each homework has number, submission date and time, due date, marks.
Each quiz has number, marks, date.
You have to store also number and marks for the exams (midterm and final).
Students enroll to courses and you have to store their final grade.
Database should also store the various activity grades for each student.
SQL Queries:
1. Find all students who are taking “math” course along with their grades in mid and final exam.
2. Find the students who submitted a homework after the deadline in “biology” course.
3. Find the quizzes of “math” course along with their dates and marks.
4. Show the total grades of each course that a student has taken.
5. Show the students who obtained grades less than 60 in “physics” course.
I. I.
Instructions:
Explanation / Answer
Please find my answer:
Course
C_Id, c_title, credit_hour
SQL: Create table Course (c_id varchar(10) primary key, c_title varchar(20), credit_hour date not null);
Student
S_Id, s_name, s_email
SQL: create table student (s_id varchar(10) primary key, s_name not null varchar(20), s_email varchar not null (10));
Homework_Grade
H_Id, h_submission_datetime, h_due date, h_marks
SQL: Create table homework_grade (H_id varchar (10) primary key, h_submission_datetime not null date, h_due_date not null date , h_marks not null int, s_id varchar(10) foreign key references student (s_id) , c_id varchar(10) forign key references course (c_id));
quizz,
q_id, q_marks,q_date
SQL: create table quiz (q_id varchar (10) primary key, q_marks not null int, q_date not null date, s_id varchar (10) foreign key references student (s_id), c_id varchar (10) foreign key references course (c_id));
Grade
Id, type, marks
SQL: create tabl grade (g_Id varchar (10) primary key, g_type not null varchar(10), g_marks not null int, s_id varchar (10) foreign key references student (s_id), c_id varchar (10) foreign key references course (c_id));
Answer 1:
Select s_name from student, course, grade where grade.sid= student.s_id and grade.c_id=course.c_id and c_title= “math” and g_type any (“mid”, “final“);
Answer 2:
Select s_name from student, homework_grade, course where homework_grade.sid= student.sid and homework_grade.cid=course.cid and c_title= “Biology” and h_due_date < SYSDATETIME();
Answer 3:
Select q_id, q_marks,q_date from quiz,course where quiz.c_id=course.c_id and c_tile=”math”;
Answer 4:
Select sum (g_marks), c_id,c_name,s_name course,grade,student where course.s_id=student.s_id and course.c_id= grade.c_id group by c_id,s_name;
Answer 5:
Select s_name from student, grade ,course where grade.s_id=student.s_id and course.g_id=grade.g_id and g_marks < 60;
I have answered your question. Please do not forget to give a positive feedback to the answer. Thank you.