This is a database containing a list of students (STUDENTS), a list of departments (DEPARTMENT), a list of faculties (FACULTY) in the university, a list of assignments (ASSIGNMENT) and a table representing the relationship between which students have completed which assignments and which departments the assignments are associated with. A student enrolled in one department can work on an assignment from a different department (e.g. taking a course offered from a different department). The relationships in this database are shown in the screenshot below: Assume the data in the database can change. The data in the above screenshot is just an example. Using the database shown above, create a Microsoft Word document containing the SQL statements (as shown in the lecture materials) to do the following: a. Find the Student Number, Last Name and Birth Date for all students whose Gender is male. Sort the result by Birth Date in ascending order. b. Write a query to list any Students that live on West Ave. List all fields for the student in the STUDENT table. This query must still work correctly if the address in the STUDENT table change or new fields are added to the table. c. List the Student’s First and Last Names as well as the name of Department the student is enrolled in for all students born after March 2nd, 1996. d. Modify the student information for Student Number equal to 250012345 to your first and last name, address, birth date and gender. (only your first/last name must be real, you can make up values for address, birth date and gender if you wish). Do not alter the value of ClassYear or EnrolledInDepartmentID in anyway. You must not alter any other student’s data in this query. e. Find all Students with an Assignment Grade between 20 (inclusive) and 50 (exclusive). This means the grade must be 20 or greater and under 50 (a grade of 50 would not be included). Include the Student’s First Name, Last Name, Assignment Name, and Assignment Type in the results (and no other fields). Students should be listed multiple times if they have multiple assignments that match these criteria (once for each assignment matching the criteria). f. Find the number of assignments with a failing grade (less than 50) that belong to a Department in a Faculty that is a not professional school (does not have a check mark for the ProfessionalSchool field in the FACULTY table). The resulting field that contains the number should be named “Fails”. Hint: A check mark in a Yes/No field is equal to True, no check mark is equal to False. g. Find a list of Assignments you have completed. Include the Assignment Name, your grade, the Name of the Department that gave the assignment and the Name of the Faculty of the Department that gave the assignment (note that this is not necessarily the same as the Department/Faculty you are enrolled in). You must use your first name and last name in this query and NOT your student ID. HINT 1: it is very easy to tell if a value is stored as a number or as text in an MS Access table. -if the value is right justified (pushed to the right) then it is stored as a number. -if the value is left justified (pushed to the left) then it is stored as a text. -if a checkbox is shown, then it is stored in a Yes/No field. -BirthDate in the STUDENT table is a Date/Time type. HINT 2: remember how field names with spaces are treated differently from field names without spaces. HINT 3: remember, round brackets are required to be used with all INNER JOIN statements. The format (how the SQL statements are written) MUST match the style shown in the notes. Each SQL reserved word MUST appear on their own line and in capital letters in the document. Each SQL Statement MUST be indented as shown below. You will lose major marks otherwise. example: SELECT something FROM ( somewhere INNER JOIN somewhere else ON some condition ) WHERE some condition is true; This is non-optional. You MUST use this standard. You will be graded on adhering to this standard. You MUST write the SQL without the use or aid of any electronic method. For example: You can NOT use MS Access Query Builder to create the SQL graphically and then copy or type in the result to your Word document. You will be graded on adhering to this standard. You must identify yourself on the document. You will lose marks if this is missing. Somewhere visible on the beginning (top) of the Word file you must include: - your first and last name - your Western ID (see below for a description of your Western ID) - your student number Save your SQL statements in the Word file named "youraccountname_sql.docx” and attach the file to your submission. Project 2: Queries using Query Designer in Microsoft Access Create a brand new, blank database and name it “youraccountname_customers.accdb”. (substitute youraccountname with your actual account name.) Using the XML Import utility in MS Access import the data in the file Assign5.xml. You will need to have the Assign5_F20.xsd file in the same directory as Assign5.xml. This will import five (5) tables with data into your database. Update the customer table for customer_number=1 by replacing the Customer First and Last Name with your own (you may do this by hand). Create the following relationships in your database AND enforce referential integrity for each: 1. customer_number in the Customer table related to the custID in the Orders table. 2. isbn in the Books table related to the BookISBN in the Writes table 3. isbn in the Books table related to the BookISBN in the Orders table. 4. authID in the Authors table related to the AuthorID in the Writes table. Everything else is set by this XML and the schema. No further action or intervention is required by the students on the MS Access file. Create a query using the Graphical Query Design Tool in the Create tab in MS Access for each of the following. (You MUST name each query object using the names: Query A, Query B, Query C, Query D and Query E respectively) A. Find the isbn, title, publication year, and retail price for all of the books from 180 series of the ISBN ordered by publication date. Books from the 180 series all have an isbn number that begins with a “0-180”. B. Find the ISBN, title and order Date for all of the books order by the customer whose ID is 11. C. Find the customer number, first name and last name for all customers who ordered a book which was published before 1970. In addition to showing the customer number, customer first name and customer last name, show the book title and order quantity as part of the resulting dataset. Order this by book title in ascending order. If a customer has ordered more than one book published before 1970 that customer will appear more than once in the resulting dataset. D. Find the total number of orders of books which were published before 1970. Customers who have ordered more than one book published before 1970 will be included in the answer for each book that they have ordered. E. Find the title, author’s name, Order Date and quantity for all of the books that you have ordered (use your last name and first name in the query not the Customer ID). List the books in descending order by retail price. Save your database in the file named "youraccountname_customers.accdb” and submit the file through OWL. Project 3: Information Systems Questions regarding your Company Create a one page MS Word document and complete the following questions pertaining to the business you described in Assignment One (1). Each answer must be comprehensive (more than one sentence). Each answer requires at least four sentences. The entire Project 3 should be at least approximately 400 words. It is expected that some thought and explanation is included in this section. 1.) Does your company have a Web Presence: - why or why not? - suggest a domain name (i.e. www.workingbytes.com) for your company 2.) Is your company’s primary source of advertising the web? - If yes, why, if no, then what is your company’s main way of advertising.. 3.) How important is it for the head of the company (president or owner or CEO – whichever title) to be ‘computer literate’? - What is the minimal level of understanding of software and hardware would you need to run a company of 200 to 1,000 employees? The format of these document should be similar to format you used in Assignment One (1). Place your name, followed by the company name at the top. At the top of the document, include your name, Student number and Western ID (the first part of your Western email (i.e. if your email was - derntwis@uwo.ca your ID would be - derntwis) Fill in the required information after. Formatting is not important as long as the document is easy to follow: This documents must be a Word file saved and submitted as a .docx file The youraccountname must be your Western Account Name. Submission Instructions: You must upload, attach and submit, via the CS1032 course website in WebCT, the following 3 files: • youraccountname_sql.docx • youraccountname_ customers.accdb • youraccountname_yourcompanyname_A5.docx NOTE: This description of the assignment contains instructions that tell you to create files with names that have a specific format. In these file names; the “youraccountname” is your UWO username. It is your responsibility to ensure the correct files have been submitted in OWL. Please check and make sure you have received the confirming email and then check that the three (3) files (you must submit three (3) files for this assignment) have been uploaded correctly. If you submit incorrect or incomplete files, there will be no re-sends or re-grading. It is your responsibility to check. No exceptions, no excuses. You must do ALL THREE (3) Projects in this assignment. This is Assignment 5, comprised of three (3) parts, Project 1, Project 2, and Project 3. All three projects are to be completed and submitted. It is your responsibility to regularly backup your files. No extensions will be given for lost or corrupt files. REMEMBER: It must be your own work. You can NOT work in groups or share work. You cannot use a template or “starting point” from another person (even if they are your tutor). We will be checking for any academic dishonesty and the penalties as outlined in the course outline will be applied.