| 8 | Thu 7 May |
Prepare an SQL command script with statements to query the tables in your
ManyToMany database created for lab 7. Submit this script as an attachment to an
email sent to tom.brown at the domain tamuc.edu . The email subject line is to
have our course code, your leoMail username(e.g. mWidenius), and the lab number 8.
- Show create table statements for the two independent classes and the
associative entity. (3 pts)
- Query and project all columns from each of the tables. Include an
order by clause on the querys to highlight those columns
designated as primary keys; (3 pts)
- Prepare two nested querys to document the m:m relationship:
- 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). (3 pts)
- 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 pts)
- 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 pts)
- 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. (2 parts: 4a, 4b; 4 pts total)
Note: The SQL command script should be stored as a text file(.txt) containing
only valid query commands(no prompts, no query results). Scripts are
referenced on the web page under class exercises, SQL program examples
and in text chapters 2 and 11.
|
| 7 | Thu 30 April |
Create a Database named ManyToMany with three tables: two for independent entity
classes, and a third for an associative entity class. Choose entity classes from
the page M:M Database Choices.
Reserve your entity classes by 22 April. Create then export the database to a
text file and submit as an email attachment sent to tom.brown at the domain
tamuc.edu. The email subject line is to have our course code, your leoMail
username(e.g. mWidenius), and lab number 7. Within the email body, list web
address(es) for the source of data* for your independent entity classes.
- 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(have at least one with auto_increment and
zeroFill), 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. (8 points)
- 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. (6 pts)
- 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. (6 pts)
Note: Database backup and restore (export and import) is referenced on the
web page under class exercises and in text chapter 19.
|