The primary keys are identified below. The following data types are defined in the SQL Server. tblLevels Level – Identity PK ClassName – text 20 – nulls are not allowed tblPool Pool – Identity PK PoolName – text 20 – nulls are not allowed Location – text 30 tblStaff StaffID – Identity PK FirstName – text 20 MiddleInitial – text 3 LastName – text 30 Suffix – text 3 Salaried – Bit PayAmount – money tblClasses LessonIndex – Identity PK Level – Integer FK SectionID – Integer Semester – TinyInt Days – text 20 Time – datetime (formatted for time) Pool – Integer FK Instructor – Integer FK Limit – TinyInt Enrolled – TinyInt Price – money tblEnrollment LessonIndex – Integer FK SID – Integer FK (LessonIndex and SID) Primary Key Status – text 30 Charged – bit AmountPaid – money DateEnrolled – datetime tblStudents SID – Identity PK FirstName – text 20 MiddleInitial – text 3 LastName – text 30 Suffix – text 3 Birthday – datetime LocalStreet – text 30 LocalCity – text 20 LocalPostalCode – text 6 LocalPhone – text 10 Implement this schema in SQL Server or access (you will need to pick comparable data types). Submit a screenshot of your ERD in the database. Explain the relationship rules for each relationship (e.g., tblEnrollment and tblStudents: A student can enroll in many classes). Identify cardinality for each relationship, assuming the following rules: A pool may or may not ever have a class. The levels table must always be associated with at least one class. The staff table may not have ever taught a class. All students must be enrolled in at least one class. The class must have students enrolled in it. The class must have a valid pool. The class may not have an instructor assigned. The class must always be associated with an existing level. Which tables are weak and which tables are strong (covered in an earlier chapter)? Which of the tables are non-identifying and which are identifying?

The primary keys are identified below. The following data types are defined in the SQL Server. tblLevels Level – Identity PK ClassName – text 20 – nulls are not allowed tblPool Pool – Identity PK PoolName – text 20 – nulls are not allowed Location – text 30 tblStaff StaffID – Identity PK FirstName – text 20 MiddleInitial – text 3 LastName – text 30 Suffix – text 3 Salaried – Bit PayAmount – money tblClasses LessonIndex – Identity PK Level – Integer FK SectionID – Integer Semester – TinyInt Days – text 20 Time – datetime (formatted for time) Pool – Integer FK Instructor – Integer FK Limit – TinyInt Enrolled – TinyInt Price – money tblEnrollment LessonIndex – Integer FK SID – Integer FK (LessonIndex and SID) Primary Key Status – text 30 Charged – bit AmountPaid – money DateEnrolled – datetime tblStudents SID – Identity PK FirstName – text 20 MiddleInitial – text 3 LastName – text 30 Suffix – text 3 Birthday – datetime LocalStreet – text 30 LocalCity – text 20 LocalPostalCode – text 6 LocalPhone – text 10 Implement this schema in SQL Server or access (you will need to pick comparable data types). Submit a screenshot of your ERD in the database. Explain the relationship rules for each relationship (e.g., tblEnrollment and tblStudents: A student can enroll in many classes). Identify cardinality for each relationship, assuming the following rules: A pool may or may not ever have a class. The levels table must always be associated with at least one class. The staff table may not have ever taught a class. All students must be enrolled in at least one class. The class must have students enrolled in it. The class must have a valid pool. The class may not have an instructor assigned. The class must always be associated with an existing level. Which tables are weak and which tables are strong (covered in an earlier chapter)? Which of the tables are non-identifying and which are identifying?

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Related questions

Question

The primary keys are identified below. The following data types are defined in the SQL Server.

tblLevels
Level – Identity PK
ClassName – text 20 – nulls are not allowed

tblPool
Pool – Identity PK
PoolName – text 20 – nulls are not allowed
Location – text 30

tblStaff
StaffID – Identity PK
FirstName – text 20
MiddleInitial – text 3
LastName – text 30
Suffix – text 3
Salaried – Bit
PayAmount – money

tblClasses
LessonIndex – Identity PK
Level – Integer FK
SectionID – Integer
Semester – TinyInt
Days – text 20
Time – datetime (formatted for time)
Pool – Integer FK
Instructor – Integer FK
Limit – TinyInt
Enrolled – TinyInt
Price – money

tblEnrollment
LessonIndex – Integer FK
SID – Integer FK (LessonIndex and SID) Primary Key
Status – text 30
Charged – bit
AmountPaid – money
DateEnrolled – datetime

tblStudents
SID – Identity PK
FirstName – text 20
MiddleInitial – text 3
LastName – text 30
Suffix – text 3
Birthday – datetime
LocalStreet – text 30
LocalCity – text 20
LocalPostalCode – text 6
LocalPhone – text 10

Implement this schema in SQL Server or access (you will need to pick comparable data types). Submit a screenshot of your ERD in the database.

Explain the relationship rules for each relationship (e.g., tblEnrollment and tblStudents: A student can enroll in many classes).
Identify cardinality for each relationship, assuming the following rules:
- A pool may or may not ever have a class.
- The levels table must always be associated with at least one class.
- The staff table may not have ever taught a class.
- All students must be enrolled in at least one class.
- The class must have students enrolled in it.
- The class must have a valid pool.
- The class may not have an instructor assigned.
- The class must always be associated with an existing level.
Which tables are weak and which tables are strong (covered in an earlier chapter)?
Which of the tables are non-identifying and which are identifying?

Expert Solution

Trending now

This is a popular solution!

Step by step

Solved in 2 steps

SEE SOLUTION Check out a sample Q&A here

Knowledge Booster

Learn more about

Need a deep-dive on the concept behind this application? Look no further. Learn more about this topic, computer-science and related others by exploring similar questions and additional content below.