Microsoft Word - IE 332 Spring 2020- Weekly assessment 3 (3).docx 1. Create relational database (30 Pts.): Note: Submit Access file. a. Create the tables in Access Software b. Create necessary...

full answer needed



Microsoft Word - IE 332 Spring 2020- Weekly assessment 3 (3).docx 1. Create relational database (30 Pts.): Note: Submit Access file. a. Create the tables in Access Software b. Create necessary relationships between all tables (make sure data integrity is applied). c. Insert data records in the tables, each table should include at least 7 records in each of its field and maximum of 15 records. (Each student will create and insert his/her own records) 2. Creating queries (20 Pts.): Note: Show results in the software and the report file. a. Display the first and last name of the student, the courses taken, the credits offered for the course and the grade received. b. Display the first and last name of the student, his/her major and the first and last name of his/her advisor. c. Count the number of students in registered in the database. d. The total number of credit hours of all courses in the database. Q2. (35 Points) Use the following table from Access, students table, to only fill the below query forms for each question: Note: for this question, your answer should be handwritten to fill the query forms below as you apply it on Access, no need to submit file. A) (7 Pts.) Who are Junior (JR)? Include Student Name, Class and Major1 fields in the output, and sorted alphabetically (A-Z) based on Last Name B) (7 Pts.) Who has a last name beginning with the letter A? Include Student Name, Class and Grade fields in the output. C) (7 Pts.) Who is a Senior and MG Major? Include Student Name, Class and Major 1 fields in the output. D) (7 Pts.) Who is a Junior or Senior and a Mgmt. (MG) major? Include Student Name, Class and Major 1 fields in the output. Result: E) (7 Pts.) The average GPAs of all students in this course: Q3. (15 Points) Professor Murray has decided to expand the functionality of the Team Information Database so that it can store the team records for ALL the students in the class. Your job as a database designer is to create the database according to the business rules and specifications Professor Murray provides. You should use the Team Information Database as a starting point for the design. Here are the requirements for the database that you should base your design upon. · Each lab section has multiple teams · Each team has multiple members, but a member can only be on one team. · Each TA is responsible for multiple lab sections In addition to the data already stored in the database, Professor Murray also wants to track the following. Next to each attribute, indicate the appropriate data type to use in Microsoft Access. · Team Name _____________________________ · TA ____________________________________ · Lab Day/ Time ________________________________ · Lab Project Name____________________ · Initial Design Grade (score between 0-5) _______________________________ · Initial Design Comments · Final Project Grade (score between 0-30) _______________________________ · Lab Section _____________________________________ - Project Name ___________________________ · Lab Location ____________________________________ · Member Name ___________________________ · Member ID_________________ · Member Last Name____________________________ Given the previous requirements, and the hint relationship form below, you need to: 1. (8 Pts.) Assign the correct Fields, Records, in for each table on the blank Entity Relationship Diagram below 2. (7 Pts.) Relationships between tables and primary key, foreign key on the blank Entity Relationship Diagram below. Note: enter the letter “P” next to all of the primary keys and place the letter “F” next to all of the foreign keys in the diagram. 2 | P a g e 2 | P a g e