MBI 635 Example Assignment Answers

Cincinnati String Academy

Cincinnati String Academy teaches violin, viola and other stringed instruments to young children. The classes are structured in sessions that start and end at a given date. A music student can enroll for multiple classes in the same session. A class is a particular time slot within a session, and this allows students to enroll in multiple classes in the same session. The fee is based on the number of classes enrolled. A class can have a maximum of 10 students. A student can take a maximum of 3 classes. A student can be in the roster without currently enrolling in any class and a class can exist without a single student while waiting for student enrollment before the registration date.

One Liner: Students enroll in classes

ER Diagram:

MBI 635 Example Assignment Answers Image 1

Four Statements:

Maximum Cardinality:

One student can enroll in up to 3 classes.
One class can have up to 10 students enrolled. Minimum Cardinality (Participation):
A student need not enroll in a class.
A class need not have any students.

Number of Tables: 3

There is a many-‐to-‐many (M:N) relationship between students and classes. Therefore, we need three tables: one for students (Students), one for classes (Classes) and a bridge table (Enrollment) that connects them with one-‐to-‐many relationships (1:M).

The primary keys of the Students (studentID) and Classes (classID) tables are both posted as foreign keys in the bridge table, Enrollment. In addition, these columns (studentID, classID) make up the composite primary key for the Enrollment table.

Cars

A car is assigned to only one department and department can have multiple cars assigned to it, with a maximum of 10 cars. A car is always assigned to a department but a department may not be assigned any cars.

One Liner: Departments have cars assigned to them ER Diagram:

MBI 635 Example Assignment Answers Image 2

Four Statements:

Maximum Cardinality:

One car is assigned to only one department.
One department can have up to 10 cars assigned to it. Minimum Cardinality (Participation):
A car must be assigned to a department.
A department need not have any cars assigned to it.

Number of Tables: 2

There is a one-‐to-‐many relationship (1:M) between departments and cars. Therefore, we need two tables: one for departments (Department) and one for cars (Car).

The primary key of the Department table (deptID) is posted as a foreign key in the Car table.

Employees

An organization wants to maintain information on employees who are married to another employee within the company. This spousal relationship needs to be maintained in a table. How would you do it?

One Liner: An employee is married to another employee ER Diagram:

		is married to
(1,1)		(0,1)
			Employee
	PK		employeeID

			firstName
			lastName
			address
	FK1		spouseID
			deptNo

Four Statements:

Maximum Cardinality:

One employee can be married to only one employee.

One employee can be spouse of only one employee. Minimum Cardinality (Participation):

An employee need not be married to an employee.

An employee need not be the spouse of an employee.

Number of Tables: 1

There is a one-‐to-‐one (1:1) recursive relationship between employee and employee (spouse). Therefore, we only need one table: Employee. The spouseID column in the Employee table contains the employeeID of a given employee's spouse (if also an employee of the same company).

The primary key of the Employee table (employeeID) is also posted as its foreign key.

Music Albums

A music album has a lead artist. A lead artist can have multiple albums. An album must have a lead artist, but a lead artist can exist (as in preproduction) without any albums. The attributes of a lead artist are name, address, city, and agent telephone. An album has a product code, production date, and price.

One Liner: A lead artist has albums

ER Diagram:

MBI 635 Example Assignment Answers Image 3

Four Statements:

Maximum Cardinality:

One lead artist can have multiple albums.
One album can have only one lead artist. Minimum Cardinality (Participation):
A lead artist need not have an album.
An album must have a lead artist.

Number of Tables: 2

There is a one---to---many (1:M) relationship between lead artist and album. Therefore, we need two tables: one for lead artist (LeadArtist) and one for album (Album).

The primary key of the LeadArtist table (artistID) is posted as a foreign key in the Album table.

Parts

A company has two types of parts manufactured and purchased parts. Manufactured parts have production capacity and plant location. Purchased parts have vendors, price and shipping location. All parts are either purchased or manufactured. The company has a policy that it would not buy the parts that it has the capacity to produce. How would you model this scenario? Please use all appropriate notations in your diagram.

One Liner: A company has parts that can either be manufactured or purchased. EER Diagram:

AllParts

PK partNo

partName

partType

{'Mandatory, Or'}

ManufacturedParts PurchasedParts

prodCapacity vendor

plantLocation price

shipLocation

Narrative Explanation:

A company has parts. This corresponds to the superclass (AllParts). Of these parts, there are two different types, manufactured and purchased. These types correspond to distinct subclasses (ManufacturedParts, PurchasedParts) within the superclass. All parts that the company has must either be manufactured or purchased. This implies that every member of the superclass (AllParts) must participate as a member of a subclass, which is a mandatory participation constraint. Inotherwords,thismeansthatthereisNOotherpartsbesidesthe manufacturedandpurchased. The company does not buy parts that it can make. This implies that a member of the superclass (AllParts) can be a member of only one subclass, i.e. member of ManufacturedParts or PurchasedParts, but not both. This is a disjoint constraint (OR).

The two tables that we need are ManufacturedParts and PurchasedParts. Since all parts in superclass is going to be in one of the subclasses and only in one of the subclasses, the above two tables decision makes sense. Go over the table in Figure 17.2 and understand the number of tables needed in each case.

USA Pages

USA Universities Questions

USA Universities Sample Assignment

NET 289 case study 3

Not the Exact Question you were looking for ? Post your question for assignment help and get instant help on your homework and assignment questions from our experts