Question

The following tables contain the data for courses, enrollment, and students. COURSE ENROLLMENT ourse Number Title AC202 FL101 tudent ID 77349993 77349993 77349993 77349993 77349993 77349993 77349993 00005555 Instructor Credits ourse No rade uffsaton C202 S303 105 MA I 30 M204 H150 Y123 IS326 IS326 IS326 IS326 IS326 IS326 IS326 IS326 IS326 IS326 IS326 M204 ccountin sic atabase IS326 FL105 FL503 rank omposition Mauffler A102 Basic Math tatistics ournalism Hernandon asic circuit Shockl nnis esisitorT ounter M204 34556677 110000 119800 348282 110001 55119800 339837 33448887 2551288 22551288 izzer PY123 sc STUDENT Student ID First Name Last Name Major Status Gender Date of Birth Café Balance 0348282 Tirilee 234556677 777349993 John 800005555 Patrick S23 $0 12/01/1974 03/03/1976 02/03/1974 07/08/1974 er oons usiness usiness usiness nd. irkland 01/05/197 9800 9110000 6110001 555119800 08/28/1947 06/19/1978 1 09/06/1978 04/05/1955 112/25/1974 08/07/1974 05/05/1948 $323 ac $434 re ohnson usiness 339837 acob hwartz 33448887 222551288 Nanc rt

Question 1.Write a SQL statement for each of the following questions based on the above tables (50 Points).

1) Create “Enrollment” table.

2) Change the credits of “Database” course from 2 to 3.

3) List the course titles (not Course_No) and grades John Doe had taken.

4) Calculate average of Café Balance, and name new field as Average Balance.

5) Create a roster for “Database” course (list student ID, last name, first name, major, and status for each student enrolled the course).

6) Find number of students for each major, and name new field as No of Student.

7) Insert a new student data. You choose the data.

8) Delete the student date you inserted in Question g).

9) List student ID, first name, last name, major, course no, course title, who got the grade A.

10) List student ID, first name, and last name whose café balance is $54, $434, OR $23. Use IN operator.

Submission:

 Your answer document MUST show SQL statement for each question (No query results required).

 Put the SQL statements in Microsoft Word or PDF file and submit the Word or PDF file o Put question #, too.

Note:

 For joining tables, use “old-style” join or “Join … On” syntax (covered in 7-4c) ONLY. Other join commands will NOT be accepted. You may read page 323 if you don’t know what is “oldstyle” join.

 You don’t have to use database software to answer. But if you want, you can use any database software (MySQL, Oracle, SQL Server, etc.), by creating tables and inserting data. SQL script will not be provided.

Question 2. The Part_Supplier table contains sample data for parts and for vendors who supply those parts. Answer the questions below (50 points)

1) In what normal form is this table?

2) Draw its dependency diagram and identify all dependencies.  Yours should look like the diagram in Figure 6.3. But you don’t have to include relational schema, that is, 1NF (PROJ_NUM, EMP_NUM, PROJ_NAME…….).

3) Normalize the table so that there are no more partial and/or transitive dependencies, and draw the new dependency diagrams for the normal form.  Yours should look like the diagram in Figure 6.5.

Note:

 In drawing the diagrams, use the textbook notation

 In drawing the diagram, you can use any (diagraming) software, such as Draw.io, Microsoft Visio, LucidChart, or even Microsoft Word.

 Copy and paste the diagram to Microsoft Word or convert them PDF and Submit the Word file or PDF file o Include diagram ONLY, not entire software screen o Your submission should be readable

 Put your name on your document