1. Draw an ERD for the Parrot Palace information system.
2. Indicate cardinality.
The objective is to create an ERD that will help to represent the relationship between the birds and tricks. In order to accomplish this, it is important to use the associative entity that has the data about a specific bird and the specific trick. Since there are 50 birds and 20 tricks, the table will have hundreds of records. The key would be to use a combination of the number of birds and the number of tricks. The additional associative entity will also need to be shown. The entity will be based on the assumption that one customer might place certain requests and that any number of combinations in the BIRDTRICK could be requested by more than one particular customer. The key for the customer requests is the combination of the customer numbers, the trick requested, and the skill level needed.
3. Identify all fields you plan to include in the birds and tricks tables.
The separate fields will provide flexibility in the sorting and the report designs and allow the creation of the letters that can look more professional. For example, many of the databases are going to use six different fields for names: First Name, Salutation, Middle Initial, Suffix, Last Name, and Nickname. This type of arrangement will allow you to be able to create some mailing labels with your full name and begin the greeting with your nickname. Therefore, in the BIRD table, you should be able to point out that is better to use a date of birth instead of having to update the age field that will need updating periodically.
4. Create 3NF table designs.
There are several combination keys that are special for the two associative entities: BIRDTRICK (Bird No, Trick No, and Skill Level), CUSTOMER (Customer Number, First Name, Salutation, Last Name, Middle Initial, and Suffix. Street, City, State, Telephone, and Zip). Trick (Trick Number and Trick Name). Bird (Bird Number, Name, Size, Species, Favorite Food, and Birthdate). Customer Requirement (Customer Number, Skill Level, Trick Number, and Status).
Winfield Public Transit Authority
1. Draw an ERD for the Winfield Public Transit Authority system.
2. Indicate cardinality.
The cardinality of the entities are the drivers that might be assigned to a particular bus. It allows that there is a possibility that several drivers might be assigned to one bus throughout the day. This is going to be especially true when there are several shifts and lunch breaks.
3. Identify all fields you plan to include in the tables.
4. Create 3NF table designs.
FLEET (BUS_ID*, VIN, MODEL, MANUFACTURER, DEALER, STORAGE_LOCATION, CURRENT_LOCATION, INSPECTION_DATE)
DRIVER_INFO (EMPLOYEE_ID*, LAST_NAME, FIRST_NAME, LICENSE_NO, LICENSE_STATE, EXP_DATE)
ROUTES (ROUTE_ID*, ROUTE_DESCRIPTION)
STOPS (STOP_ID*, STOP_DESCRIPTION, STOP_DIRECTION)
Working Shoes
1. Design a code scheme that will meet the marketing manager’s stated requirements.
The combination code might allocate two different digits from the item type, a dependent number for the style that will be followed by the digits that will show the size and color of the shoes.
2. Write a brief memo to the marketing manager suggesting at least one alternative to the code she proposed, and state your reasons.
Dear marketing manager,
As per the discussion with you, the code used in the products of Working Shoes is quite old. It only shows the color and size of shoes.
So, I suggest you to use this code for color, size, style, year of manufacturing. This will help us to identify the shoe in a unique manner.
Thanks.
3. Suggest a code scheme that will identify each Working Shoes customer.
There are two code schemes that can be used in this situation. The first one is the straight sequence codes automatically assigned by the system. The second one is that the codes will combine alphabetic and sequence blocks. Many firms like the second one because it helps to provide he indication of the customer’s last name.
4. Suggest a code scheme that will identify each specific order.
The order entry system will automatically assign the order numbers more dependently. The company might want to code the source of the order so that code will need to identity the character of the purpose.
References:
Rosenblatt, Harry J. (2013). Systems Analysis and Design. Cengage Textbook. Kindle Edition.
