SQL Chpt 3

Read the given business scenario. Draw the entities HAIRSTYLIST and CLIENT. List the attributes associated with each entity and specify whether they are mandatory or optional. Identify the UIDs. Write out the relationship in English, including optionality and cardinality. Follow the diagramming conventions discussed. "In our salon, we have a number of hairstylists. They are all salaried employees, so we keep a record of their first name, last name, address, phone number, social-security number, and salary. During the course of a day, a hairstylist may see several clients. On a slow day, a hairstylist may not work on anyone at all. We have several walk-in clients, and they each get assigned to one hairstylist. We just ask for their first name. We also have customers who call to make an appointment. When they do this, we ask for their first name, last name, and phone number. We also ask if they would like a specific hairstylist. If they have no preference, we assign one for them. Of course, they are allowed to switch to another hairstylist for their next visit to the salon. We are interested in tracking the daily appointments -- which stylist works on which client during a given day."

A HAIRSTYLIST may work on one or more CLIENTs. A CLIENT must be assigned to one and only one HAIRSTYLIST. HAIRSTYLIST # id * first name * last name * address * phone number * social-security number CLIENT #client number * first name o last name o phone number

Read the given business scenario. Name the relationships between ORDER and WAITER. Include the appropriate optionality and cardinality. "We assign our waiters to certain areas, except for our trainees who just observe and are not responsible for taking any orders yet. A waiter takes the orders for the tables in his area. All areas have one assigned waiter. A customer places an order with a waiter. If the customer has a question or wants to make a change to the order, he needs to request this with the assigned waiter."

A WAITER must take ORDER. An ORDER must be adjusted by one WAITER.

Read the given business scenario. Name the relationships between EMPLOYEE and JOB. Include the appropriate optionality and cardinality. "We have a lot of employees who handle one or more different jobs. We'd like to keep track of who is working on which job. Although employees can help each other, a job is assigned to one employee and is the ultimate responsibility of that employee. All of our employees have at least one job. However, jobs exist that are not yet assigned to anyone."

An EMPLOYEE may handle one or more JOB. A JOB must be assigned to one EMPLOYEE. A JOB is the responsibility of one EMPLOYEE. An EMPLOYEE may assist another EMPLOYEE with a JOB.

A property of an end of a relationship between X and Y that describes how many of X is related to Y is known as _________.

Cardinality

Identify the symbols for cardinality.

Crows foot - many / one or more single toe foot - one

The language or statements used to describe relationships between entities in an entity-relationship diagram is known as ______________.

ERDish

Read the given business scenario. Draw the entities BAND and MUSICIAN. List the attributes underneath each entity. Specify whether they are mandatory or optional. Identify the UIDs. Write out the relationships in English, including optionality and cardinality. "I am an agent for several musicians and bands. A musician may be a solo performer or may belong to a band. A band will always have one or more musicians in it. Some musicians are a one-man band. However, a musician can belong to only one band. Since I schedule them for concerts and events, I need to keep track of certain information: the musician's first name, last name, address, phone number, and hourly rate. If it's a band, I need to know the band name in addition to the information I already keep for the member musicians. I've handled bands with the same name, so just to make sure I book the right band, I assign an ID to each one. The hourly rate for a band is the total of the hourly rates of its members."

Each BAND must be composed of one or more MUSICIANs. Each MUSICIAN may belong to one and only one BAND. BAND # band id * band name MUSICIAN # number * first name * last name * address * phone number * hourly rate

Read the given business scenario. Name the relationships between PARENT and CHILD. Include the appropriate optionality and cardinality. "At the end of each day, parents need to pick up their children at our day-care center. All children must be picked up by 6 p.m. A child may have two parents, but we need only one of them to come and pick up the child. We cannot release a child to anyone but that child's parent."

Each PARENT may pick up one or more CHILDren. Each CHILD must be picked up by one or more PARENTs.

Read the given business scenario. Draw the entities TEACHER and COURSE and CLASS. List the attributes underneath each entity. Specify whether they are mandatory or optional. Identify the UIDs. Write out the relationship in English, including optionality and cardinality. "We have several teachers at our school. A teacher can be assigned up to three classes per semester. If a teacher is on sabbatical, he doesn't teach that semester. We keep a record of the teacher's first name, last name, address, phone number, and email address." "Our school offers many courses -- such as Data Modeling, Introduction to SQL, Trigonometry, Physics, and Biology. Each course has a code. For example: Data Modeling would be DM001, Trigonometry would be TR004, etc. During each semester, a course may be taught in several classes -- so there could be two classes of Physics, three classes of Biology, etc. Each class can be taught by only one teacher. We assign a unique ID for each class, and we also keep track of the day it is taught, the time, and the classroom."

Each TEACHER may be assigned one or more CLASSes. Each CLASS must be assigned to one and only one TEACHER. A COURSE may be offered through one or more CLASSes. Each CLASS must be an offering for one and only one COURSE. TEACHER: # teacher id, * first name, * last name, * address, * phone number, * email address COURSE : # code, * description CLASS: # id, * day, * time, * classroom

Read the given business scenario. Name the relationships between TEACHER and STUDENT. Include the appropriate optionality and cardinality. "Some students request remedial help in certain subjects, such as math. We can assign a tutor who can work with the student after class. Some of our teachers agree to be tutors. If several students need tutoring in one subject, then we assign them to the same teacher. If a student needs tutoring in several subjects, then he will probably be assigned to several different tutors."

Each TEACHER may tutor one or more STUDENTs. Each STUDENT may be tutored by one or more TEACHERs.

A grid-like drawing that can be used to discover and record relationships between entities in an entity- relationship model is known as a _________________.

Matrix

A property of an end of a relationship between X and Y that describes whether X must be or may be related to Y is known as _______________________.

Optionality

A connection or association between objects is known as a _________________.

Relationship

A four-sided visual element with rounded corners, used to represent an entity in an ERD is a __________________.

Softbox

Identify the symbols for optionality.

solid line - must be dotted line - may be