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.