Database Systems Draw the model

1. For each of the following concepts, show a real world example (other than ones presented in the text or in class session, give the justification/explanation for each of your examples, Draw the model for.

1a. a recursive relationship. Explain the meaning of this relationship n a few words

1b. an entity that is weak but ID Independent in a relationship . Explain the meaning of this relationship n a few words

1c. an entity that is weak and ID-dependent in a relationship. Explain the meaning of this relationship n a few words

2. Draw an ER Diagram (ERD) for the following situation:

A bus company owns a number of buses and has a contract with a town’s Board of Education. Each bus may be assigned to one or more routes. Each route has a unique label/ID, certain mileage, and passes through a number of stops. A stop is identified by its stop-code and street address, and can be on multiple routes. A route connects to a school. A school’s students may come from different routes every day. Board of Education needs to keep track of students information such as name, and grade as well as the stop and route she/he is on. Each school has a name, phone number of contact and an address. The company needs to keep track of estimated numbers of students to be picked up/dropped off at each stop and updates these numbers each year based on the information given by the Board of Education. One or more drivers are signed to a route. A driver’s license and their cell phone number are important information for the company. There are a few garage(s) where buses are kept. The company needs to keep track of the location and the contact of each garage. Each bus are identified by its registration number and has a capacity of passengers. The information about a bus also includes its model and year. Each bus may be driven by different drivers so a daily log of driving is necessary to keep track who drives which bus on which date and time period as well as the routes. The log also records any incidence as note of the day if there is any.

3. Consider the following environment of a university. There are academic departments. Each department has a chairperson. Each department offers a set of courses. Some courses are currently offered. Some courses need prerequisite. An offering of a course is called a class or class section. Some courses have multiple class sections. A class is taught by a professor. It meets at a particular location. Some classes are scheduled in lab rooms that has certain number of stations/seats. University staffs are in charge of lab management. Not all departments offer the summer courses. Each professor is assigned to a single department and has a rank. Each student has an academic status (undergraduate, graduate, non-degree). The university requires GMAT or GRE scores for graduate students. Each student is currently enrolled in a number of classes. Some undergraduate students are in double majors. Some students are performing better because they are hard working. Students’ financial aids depend on their academic performance. Graduate students can have individual faculty as their advisor in writing thesis. Students may join some clubs registered in university. Some clubs are free but some require fees. The university also needs to keep track of club memberships.

3a. Draw an ER model showing the entities, attributes and the relationships in the environment. Indicate the identifiers (keys) of all entities (including weak entities). Indicate some attributes for all entities. For each relationship, indicate the cardinalities (maximum and minimum)

3b. Which part of above description you cannot represent in your E/R model?

3c. Convert the ER model into a set of tables. Clearly indicate the primary key and foreign key (if there is any) in each table.

4. The Community Dance Academy (CDA) teaches social dancing and offers both private and group lessons. Jefferson charges $45 per hour per student for a private lesson and charges $600 for a student attending a group lesson that last 8 weeks. Private lessons are offered everyday (from noon until 9 p.m.). Group lessons are only offered in the evenings on weekdays. Each group lesson is scheduled a classroom depending on the size of the group. All private lessons are scheduled in a designated room.

Jefferson employs two types of instructors: full-time salaried instructors and part-time instructors. The full-time instructors are paid a fixed salary per month and the part-time instructors are paid a set amount for teaching a particular class.

In addition to the lessons, Jefferson sponsors social dance party each weekend. The admission charge is $5 per student. The purpose of the dance parties s is to give the students a chance to practice their skills. No food but non-alcohol drink are served. Jefferson tries to get some local merchants to sponsor the parties.

Jefferson wants to develop an information system to keep track of students and the classes they have taken. Jefferson’s managers also want to know how many and which types of lessons each teacher has taught and to compute the revenue generated by offering lessons and social dance parties