in this class you are expected to complete a group final assignment th
Search for question
Question
In this class, you are expected to complete a group final assignment. This project will cover
conceptual modeling by ER or EER, logical modeling, normalization, and functional
dependencies. (This is for those groups that you formed in class). You cannot work on this
individually. It must be completed as a group work.
As you are working on this project, it is important to regularly discuss your progress with the
professor. You can always come to the office hours with your work so that we can discuss it.
Project Requirements and Description (The Mini world or UOD):
As a database architect, you have been hired by the School of the Natural and Social Sciences
(NSS) to design a database for the school. The database keeps track of the employees in each
department of the school. students majoring or minoring in programs offers by the departments
such as computer information systems, cybersecurity, data science and AI. The employees are
categorized into faculty and staff. The requirements are summarized as follows:
• Each department has employees. An employee is identified by emplid. The employee is
also characterized by role (The role is categorized into faculty or staff). An employee can
be a staff and instructor in a department. For example, a full-time staff who decides to
teach in a department of a part time basis. Also, each employee has a name, ssn, phone,
email, address, office location, and date of hire.
Each faculty has a rank, specialization, areas of research interests, and areas of teaching
interests. (For your own information faculty ranks include adjunct professor, adjunct
instructor, adjunct lecturer, instructor, lecturer, assistant professor, associate professor,
and professor.)
Staff has a position.
Each course is assigned a unique CourseID, a unique Course Name, a unique Course
Code. A course also has hours, credits, and description. A course is associated with a
department.
A Department has a unique department id. A department also has a name, phone, and
office location, you also have a faculty who is the chairperson of the department, the
chairperson start date. you can also have an employee who is a secretary of the
department. A secretary can also have the start date. A department can have multiple
secretaries.
Each Course Section has a unique sectionID. A course section also has semester year,
room Number and schedule.
You also have a major. A Major is offered by a department. A major has a name such as
computer science, data science. It also has description, type of degree e.g., Bachelor of
Science, Bachelor of Arts), total number of credits required to complete the major, a list
or reference to the courses that are mandatory for students pursuing the major, a list or
reference to the elective courses that students can choose from within the major, any
prerequisites or requirements that students must fulfill before declaring or pursuing the
major and Faculty members assigned to advise students within the major, providing
guidance on academic matters, career paths. A Student: emplid (unique), First Name, middle initial, Last Name, Date of Birth, email,
phone, address, and major. A student can be do multiple majors.
Graduates (alumnus) store information about students who successfully completed their
degrees. The graduates keep track of emplid, Graduation Year, DegreeEarned, honors,
phone, and email, GPA. If the student has entered the workforce, their current job title or
occupation and the employer that employees them.
• Employer keeps track of EmployerID (unique), EmployerName, Industry and address.
We would also need to keet tracking of students cheating. A student can cheat in a course
section Each. Each cheaiting incident has date, description, resolution.
An instructor teaches a course Section. It is possible for an instructor to teach multiple
Course Sections in one semester. One Course Section is taught by one Instructor only.
A Course Section is associated with one Course. One Course can have multiple Course
Sections.
CourseSection has Students. A Student enrolls in multiple Course Sections. One Course
Section has multiple Students. We also want to track a grade earned in the course.
A when student graduates (Graduate) can have multiple employment records. An
employment record corresponds to a student's job after graduation. For the employment
record, we also want to keep the start date, end date and job title or position.
You are allowed to make assumptions to complete the requirements provided above and those
assumptions should be discussed in your project write up.
I.
Now as part of your project in response to the requirements presented above, you are required to:
(40 points) Conceptual Modeling: Create a conceptual model for the above requirements
using the ER Model (Specify your relationship types) or EER model. You can use
drawing tools to create your ER models.
II.
III.
IV.
(30 points) Logical Modeling: Convert your ER model (or EER model) to a relational
model.
(50 points) Specify all the functional dependencies that you have in your relation model
above. Next, normalize your relations to BCNF.
(50 points) Database Implementation: Based on the normalized database relations above,
create the following SQL queries.
• Write SQL queries to create the NSS database and all your relations for the
database. Add primary keys for your relations and foreign keys where necessary
and all other necessary integrity constraints.
Write queries that insert 15 or more records in all your relations you have created
above.
• Write simple select statements that retrieve records from the tables without any
condition. (1 Select statement per table)
• Write 1 simple select statement that retrieves records from all the tables using
some simple condition. (1 Select statement per table) V.
VI.
Write 3 advanced select statements that select data from two or more of your
tables. You can use exists, and, join etc.
Write a query that retrieves students who have all A letter grades.
Write a query to retrieve the names of students who have not taken more than 5
courses.
• Write 6 update statements that update certain records based on some conditions.
Write 3 statements that delete a record from a table based on some condition.
• Write a query to retrieve the names of students who were caught cheating in at
least one course.
Write a query to retrieve the names of instructors who have reported most
cheating incidents.
• Write a query to retrieve the names of instructors who have never reported any
cheating incidents.
Write a query to retrieve the name of the most recently hired instructor.
(20 points) Application implementation: You can do this part as an extra credit. You can
start this part once you have completed the required parts of the project. Using a
programming language of your choice (Such as PHP, JavaScript) write a web application
that interfaces with the database you created above. Refer to chapter 10 or 11 for help.
Note that the extra credit can only be done if you have completed all the requirements for
the project including writing your report. The Application implementation can be added
to the report later. Your applications should make sure that it achieves the following
conditions:
Able to insert data into any of the database tables.
• Update some data in a relation.
• Delete some data.
Updates data
(This forms a CRUD Application). CRUD stand for Create, READ Update and Delete)
(30 points) Final Report Write up: Your report should include the following:
•
A cover page with the project name(title), your names, course number and date
Introduction. You can use the project description and make sure you state any
assumptions here.
Entity Relationship Model Diagram (ER diagram or EER). Explain what you did.
And add your diagram also.
• Map (Convert) the ER (EER) model into a relational model. Explain the steps that
you followed to convert your ER (EER) model to relational model. Normalization: Normalize your relations. As you are normalizing each relation,
explain the process. Make sure you list all the functional dependencies.
Write SQL queries to create all your relations. (You take the queries from the
database implementation and place them here.
• Application implementation: add the pictures and images of your application
interface. Discuss your application.
• Conclusion:
What was your experience? What was easy about this project for you?
What was difficult about this project for you? Did you ask anyone for
help? Did anyone do your work? Did you take someone's work, modify it
and submit it?
What are your future plans about databases? What would you have done
differently if you had more time?
Conclusions
(5 extra credit points) For a very nicely written and edited report. This includes nicely done
diagrams.
Submission Details:
Your work must be submitted on Blackboard. No other forms of submission will
be accepted.
You are required to submit a zipped folder that contains your project write up,
queries, pictures and images, pdf or word documents or any other files of your work.
If you also do the extra credit, please include your code in the folder.
You must submit your own work. You cannot copy someone else's work.