35 pts - Modeling Many-to-Many Relationships
Ref: (Video)
Modeling M:M DB Relationships |
PowerPoint
Review
Exercise 1 from Watson Chapter 5 (p. 103 in the pdf). Complete the
exercise by creating a Visio document named WatsonCh05Exer01.vsdx and
developing crow's foot diagrams for the five models from the exercise, each
on their own tab named 1a thru 1e. Leave a copy of WatsonCh05Exer01.vsdx in your Documents
folder on the course VM but also attach a copy to this assignment in
D2L.
40 pts - Querying Many-to-Many Relationships
Ref: (Video)
Create DBs in SSMS | Notes
Ref: (Video)
Aggregate SQL Query Examples | Notes
Review
Exercise 7 from Watson Chapter 5 (p. 104 in the pdf). Complete the
exercise as follows:
- In SQL Server in the course VM, develop a database named {JagID}Donations which implements the design provided as
part of the exercise in Watson [link to graphic];
the first reference above demonstrates an
approach to this in some detail.
- Add records to the database which will allow you to test (to your satisfaction)
the queries a. thru j. from the exercise. In addition, add yourself as a donor (please use
your own name but the phone, city and state info do not need to actually be yours).
- Continuing in SQL Server, use WatsonCh05Exer07.sql as a
starting point to create queries which meet the stated requirement for a.
thru j., putting the SQL below the requirement statement. There are some
hints within the SQL file to help guide your query development but
following the guidance is not required. Save the
resulting .sql file in your Documents on the course VM but also attach a
copy to this assignment in D2L.
Leave
your {JagNetID}Donations DB in the course VM in addition to saving and
submitting your WatsonCh05Exer07.sql file via
D2L.
50 pts - DB design practice
Ref: (Video) Data
Normalization Intro | PowerPoint
Ref: Some guidance notes for
TP02.III
Review
Exercise 5 from Watson Chapter 8 (p. 178 in the pdf). From this synopsis
- Develop a crow's
foot diagram of your data model using Visio. Name the document
WatsonCh08Exer05.vsdx; leave a copy in your Documents folder on the course
VM but also attach a copy to this assignment in
D2L.
- Create an Excel spreadsheet named WatsonCh08Exer05.xlsx with a tab for
each table in your design from step 1, naming the Excel tab the same as the
table name. On each tab use the first row to enter column headings for the
table's attributes from the design in step 1. Use rows in each tab to
provide sample data for a database created from your design which reflect
- At least 5 sample customers & their phone numbers
- Registration of at least two customers with other different
customers (for potential call discounts); leave at least one customer
without any registrations and have at least one customer register at
least two other customers
- At least 6 calls; at least two should be from customers to
non-customers, at least two should be from customers to other customers
but where no registration exists, and at least two should be from
customers to other customers where a registration does exist
Place a copy of your spreadsheet in your Documents folder in the VM but also attach a copy to
this assignment in D2L.
- In the course VM's SQL Server instance, create a database named {JagNetID}PhoneManager
which implements your model from 1. above; add data from 2. above to the
database. Leave your database in the course VM.
