•     For Assignment 5: Create a Database with a Many-to-many (M:M) Relationship
  
  For your database create tables with a M:M relationship between two independent
  entity classes. Consider entity classes from the 
  M:M Database Choices. 
  
  Each independent entity should have a single theme. Attributes or columns chosen
  for each class should be atomic and essential. Use natural language names for 
  the tables and columns, minimize the use of abbreviations for identifiers and
  and data values (verify spelling). The database schema should include attributes
  for each of the major data types: int, date or year, fixed or variable-length 
  character, and decimal with fraction. The tables for the independent entities 
  should have three or more non-key attributes. The table defined for the 
  associative entity should have one or more non-key attributes.
   
  Tables created should have data types and lengths consistent with the authentic
  data. Use case consistently for the data values. Define primary and foreign keys
  to enforce entity and referential integrity.
   
  Table data should include all known instances or a meaningful subset of 
  authentic data. Also, the data stored should illustrate M:M, 1:M, M:1,
  1:1, 1:0 and 0:1 participation constraints. 
   
  Complete the following steps to show your login and environment, table design 
  and to confirm the data depict the relationships specified above:
  0) Show tables and issue "show create table" statements for the two independent 
     classes and the associative entity.
  1) Query each of the tables to project all columns. Include an order by clause
     on the querys to highlight those columns designated as primary keys;
  2) Prepare querys with nested querys to document the M:M relationship:
     a. Reference one independent entity in a superQuery that matches multiple 
        rows in the associative entity, which will then match rows in the other
        independent entity (e.g. a superQuery on Student with nested query of 
        the associative entity Enrollment with a nested query of Course to show
        some student is enrolled in multiple courses).
     b. Then reference the other independent entity in a superQuery to produce
        multiple rows illustrating the other end of a m:m relationship (e.g. 
        for one of the courses listed in result 2a, list students related
        to (enrolled in) that course.
  3) Define a join view based on a query joining the three tables. This 
     view should include columns corresponding to the primary key and one non-key
     column from each of the independent entities, plus the keys and one non-key
     column from the associative entity. Query this view with columns projected as
     indicated above and rows ordered by the key columns from the two independent
     entities.
  4) Prepare correlated subquerys with the NOT EXISTS operator to show that there
     are independent entity instances not related to the other independent entity
     (1:0, and 0:1). For example a Student that is not enrolled in any Course; and
     a Course with no Student enrollees.
  
  Note:
    For this 20-point assignment prepare a text file(.txt) containing only valid
    query commands and results; and attach this file to an email sent to tom.brown
    at the domain tamuc.edu on or before Monday 9 December. The email subject line
    is to list our course code, your name, and the assignment number.
  

          Assignment 4: Creating a Database with a 1:M Relationship
          
Create a database with one-to-many(1:M) relationship between two entity classes.
Consider choosing entity classes from the 1:M Database Choices. Each student is
to have a different schema. To avoid duplicates, reserve your choice by November
9. Collect authentic data instances for your classes, complete the SQL 
commands described below, and attach a text file with the valid commands and
results to an email to tom.brown at the domain tamuc.edu on or before November 
21. The email subject line is to list our course code, your leoMail username
(e.g. gWidenius), and the assignment number.

Each entity class in your database schema should have a single theme. Choose at
least four logical and useful attributes for each entity class. Use natural
language names for the tables and columns, and avoid codes or abbreviations for
the names and data. It is expected that the schema will include each of the 
major attribute types: integer, character or variable-length character, a date
type, and decimal with fraction. Specify data types and lengths compatible with
the data collected. Also, use case consistently for the character data loaded.

Define primary key constraints for both tables to ensure entity integrity.
Define a foreign key in the child table (or the "many" end of the relationship)
to enforce referential integrity.

For the entity classes chosen collect authentic data for all known instances or
a meaningful subset. At minimum one relationship between the one and the many 
tables should illustrate a participation constraint of "1:M"(one parent row,
many children), another "1:1", and "1:0".

Invoke the following SQL commands to demonstrate and test your design:
1) Query each of the tables. Project all columns with rows ordered by primary
   key;
2) Verify entity integrity by attempting to insert a row with an invalid primary
   key value into the "one" table, and another into the "many" table;
3) Verify referential integrity by attempting to insert a row into your "many"
   ("child") table with foreign key that has no corresponding parent row;   
4) Issue a query with subquery (nested query) to show the existence of a 1:M
   participation between a parent row and related child rows(i.e. list child 
   rows related to a single row from the parent table).
5) Join the "one" and "many" tables and project columns corresponding to the 
   primary key and a single non-key column from the "one" table, and the primary
   key, a single non-key column, and the foreign key from the child table with
   results ordered by the primary key of the "one" (major sort key) and the 
   primary key of the "many" table(minor sort key).
  
Notes:
a. Copy only valid commands and results into a single file named for your
   "leo" email address with extension .txt 
b. Practice with related class exercises should provide useful background and 
   develop skills for this assignment.
(20 points)   
 
   
   

1. Choose an entity class and locate and collect authentic data for a database
   table to be created. Submit your entity choice along with a source of 
   authentic data to tom.brown at the domain tamuc.edu on or before 24 October.
2. Identify a primary key and at least three non-key attributes for the table
   to be created. Choose natural language names for the table and columns, and
   minimize the use of codes or abbreviations for those names or table data.
   It is expected that the table will include the major attribute types: 
   integer, character, date, and decimal with fractional part. Choose data 
   types and lengths consistent with the data to be collected and stored.   
3. Create the table.
4. Insert 6 to 12 instances(rows) representing a meaningful subset of data for 
   your entity class. Character data should be in the same case: upper or lower
   or "initial cap".
5. Code a SELECT statement to query your table.
6. Remove one row from your table with a DELETE statement. The restrict
   clause should reference the primary key.
7. Issue an UPDATE statement to modify one of the nonKey column values in
   another row. The restrict clause should reference the primary key.
8. After the update, SELECT all table rows and columns with results ordered by the
   the primary key column.
Deliverable: A script with valid commands for steps 1 thru 8 stored as a text 
   file(.txt). Attach this file to an email sent to tom.brown at the 
   domain tamuc.edu on or before 31 October. A graded response will
   be returned to your leomail account.
Note: The ALTER command may be used to add a primary key, or add or modify or 
   drop a column definition, or perform table or column renaming.
(20 points)
 

Prepare SQL to update your Implement database table from assignment 1.  Apply 
querys, inserts, updates, and deletes as described below. Submit the commands 
to tom.brown at the domain tamuc.edu on or before 28 October.
0. Implement(name,cost,date-purchased,category,modelYear):
   with three rows of  table data from assignment 1:  
   'Box Blade-4 ft',429.99,'2001-05-04','grader',2000
   'Cultivator',224.99,'1981-01-23','tillage',1960
   'Finishing Mower-5 ft',1379.00,'1988-06-02','cutter','1987 	
1. If necessary, issue ALTER TABLE  commands to ensure the table structure and
   data are consistent with assignment 1. If not already included, apply an
   ALTER TABLE statement  to ensure the table has a primary key on name.
2. Issue a START TRANSACTION command so a series of update commands may be 
    rolled back.  
3. Give a SHOW CREATE TABLE schema.tableName command to document the Implement
    table structure.   
4. Code a SELECT schema.tableName to project all Implement table columns with
    rows listed in key order.
5. Insert Data:
    a. Insert a table row with these values:
        'Finishing Mower-6 ft',1599.00,'2010-04-08','cutter',2010
    b. Code a single insert statement with values for two new rows:
        'Middle Buster',138.62,'2020-03-16','tillage',2020'
        'Landscape Rake',469.99,'2017-06-25','utility',2017
6. Modify Data:
    a. Update one column in a single row. This update should reference the 
        primary key in a restrict clause.
    b. Issue a single update that will modify two columns in a single row. This
        update should reference the primary key in it's restrict clause.
   c. Code a single update statement that will modify the same column value in
       every table row for a particular group (e.g. global change for two or
       more rows with the same value in a certain column or that have values 
       within a specified range). 
   d. Issue a "restrict" query with the IN option that lists the primary key
       values for the rows updated in parts a through c.       
7. Delete Data:
    a. Delete a single row. This delete should reference the primary key in 
        it's restrict clause.
    b. Issue a single delete that will remove all rows having values in one
        column that fall within a certain range(use the BETWEEN operator).
    c. Query your updated table projecting all columns with rows ordered by key.
8. Issue a rollback, then another query to show the table has been restored. 

        
To document these steps, copy the commands given in plain text (as displayed
in the command or query window into the body of a Leomail); then email
these steps to tom.brown at the domain tamuc.edu on or before 28 September(midnight).
(20 points)
 

• For assignment 1, create a farm Implement table with columns (name,cost,date- purchased,category,modelYear) and insert these three rows:
  Box Blade-4 ft,429.99,2001-05-04,grader,2000
  Cultivator,224.99,1981-01-23,tillage,1960
  Finishing Mower-5 ft,1379.00,1988-06-02,cutter,1987
  on or before midnight(11:59pm) Saturday in the body of an email to tom.brown@tamuc.edu (20 points)

Assignments are made to develop analysis, design and development skills. It is expected that the student will begin each assignment when presented so there is time to ask questions and to finish by the due date. Solutions that meet specified requirements will earn full credit. If not submitted by the due date, the grade on the following assignment will be used twice.