Animal Shelter Solutions Essay Example

understanding of the concepts they are currently learning. The curriculum includes many examples.

The Global Fast Foods project has a rating of 2.5 and also provides a lot of guidance. It is recommended for students to reinforce their understanding of arcs, normalization, and many-to-many relationships.

The Animal Shelter project has a rating of 3 and provides some guidance. It encourages students to practice interviewing skills with the instructor, conduct online research, and create accurate and creative data models.

The Natural Science Lab project has a rating of 4 and offers minimal guidance. It encourages students to practice interviewing skills with a science teacher and create accurate and creative data models.

The Recycling Center project has a rating of 4 and offers minimal guidance. It encourages students to practice interviewing skills with an outside person and create accurate and creative data models.

All rights reserved. DJs on Demand Project 1: 1. 1. DJs on Demand Introduction Difficulty Scale: 2 The DJs on Demand project provides an introduction to a disc jockey music service. The project's purpose is to apply business rules in order to consider basic concepts before designing a database model. This project does not require database modeling interviews. The goal of this project is to ensure that all students have the same information before preparing the final presentation and creating the Entity-Relationship Diagram (ERD). Most other projects in the Project Book will require students to work on their interviewing skills, as the aim of each database modeling project is to meet the clients' needs. Project success is determined by successful interviews, creative solution creation, and an effective presentation. As an introductory project, the ERD for the DJs on Demand

project includes entities and relationships that are presented as examples in the Database Design lessons of the course. The tasks outlined in the practices and examples focus on problem recognition and methods for improving the presented example.

This project is considered complete when students have integrated the final ERD into their presentation for the instructor. The DJs on Demand project serves as a valuable tool for applying the fundamental principles taught in the Database Design course. The lessons that encompass this project include:

• Section 2 Lesson 2
• Section 3 Lessons 1 and 2
• Section 4 Lesson 2
• Section 5 Lessons 3 and 4
• Section 7 Lessons 1 and 2
• Section 15 Lessons 4 and 5

Please note that if any of these lessons are skipped, students may encounter difficulty successfully completing the corresponding practice step, as well as subsequent steps in the project.

On a scale of 1-5, the difficulty rank for this project is 2. It is recommended that instructors teach this project first as it helps students solidify their understanding of the basic concepts of data modeling. The final ERD can be found in the Solutions section of this project. 1. 2. Case Study Read the complete scenario for the DJ business below. Oracle Academy Database Design 1 Copyright © 2009, Oracle. All rights reserved. DJs on Demand “We started out as a group of friends who organized parties and customized our own music. Then we thought we’d turn it into a business to pursue our interests and earn some money.

We are known as "DJs on Demand," where all employees are partners with individual responsibilities. The project manager initiates

communication with the client to discuss the event, whether it's a birthday party, wedding, anniversary, or graduation. The date for the party or event is determined during this discussion. Afterwards, the event planner contacts the client regarding specific locations, catering, decorations, and other details. The DJ then interacts with the client to understand their music preferences. Lastly, the project manager oversees both the event planners and DJs.

He/she is also responsible for approving project-related expenses. "We have a vast assortment of CDs with multiple songs on each CD, featuring the possibility of overlapping songs. Our aim is to categorize each song by genre, such as hip hop, salsa, R&B (rhythm and blues), techno, polka, rock, jazz, new age, classical, etc." Additionally, "Depending on the occasion, we can propose an initial selection of songs to the client. However, clients are welcome to request additional songs." Furthermore, "Our clientele is expanding, and we receive a significant amount of repeat business. Customers who appreciate our work often engage us for their subsequent events."

We have busy customers who often have multiple events happening simultaneously. We utilize a list of themes to categorize these events, such as tropical themes for weddings, carnival themes for parties, or sixties themes for anniversaries. This helps us select a suitable venue and gives us an idea of appropriate attire for the DJ and other musicians. Additionally, themes help us match the right person with the job based on their expertise. Events can take place in public spaces or private homes.

The event manager visits both the public-space renter and the private-home owner and arranges the event. "To keep track of who is working on which event,

we maintain a log of the activities performed by each event planner and DJ, along with their corresponding timestamps." The following is the business scenario that outlines the relationships. "We classify all our music, including individual songs and soundtracks, based on their genre. The genres we classify them into include rock, jazz, country, classical, pop, new age, and more."

The text below outlines various aspects of DJs on Demand, including the addition of new music types, the growth of their client list, the classification of events by theme, and procedural business rules. The text also includes steps for recognizing attributes for entities in a DJ business.
```html

We can add new types as the need arises – in fact we recently added a new type for rap music. We realize that a song can really be classified under more than one type, but for our purposes we select only one main classification type for each song.

Oracle Academy Database Design 2 Copyright © 2009, Oracle. All rights reserved.

DJs on Demand DJs on Demand clients, events, and themes are listed below.

“Our client list is growing. We have a lot of repeat business – customers who like what we’ve done who ask us to work for them again. We have some very busy customers who can have more than one event going on at the same time! Each partner has some specialty or expertise – so when it’s appropriate, we like to classify our events by theme to help us assign the right person (partner) to the job. An event theme can be a beach party, medieval, carnival, retro sixties or seventies, etc. We keep adding event themes as we go.”

Procedural

business rule examples are listed below.

1. “Initial contact with the client from DJs on Demand must be made by the project manager.”
2. “Approval for travel requests to an event must be signed by the project manager for that event.”

1.3.Steps, Exercises, and Examples

Step 1 – Recognizing attributes for an entity

This step may be undertaken following Section 2 Lesson 2. Three entities that play a role in a DJ business (SONG, EVENT, and CUSTOMER) are listed as the first three column headings of the table below. The fourth column contains a collection of attributes.

```Table 1: Entity and attribute assignment

SONG
- Title
- Description
- Release date

EVENT
- Venue

CUSTOMER
- First Name
- Last Name
- Phone Number
- Type (Could be an attribute for Customer)

Step 2 – Understanding relationship between SONG and TYPE
This step may be undertaken following Section 3 Lesson 1. You have learned about the relationship between SONG and TYPE. Answer the following questions:
• Must every SONG have a TYPE?
- DJ's on Demand (check mark here)

• Can you have a SONG that doesn’t fall under any one TYPE? What would you do in this case?
- DJ's on Demand (check mark here)

• Must every TYPE describe or classify a SONG?
- DJ's on Demand (check mark here)

• Why would we want to have a TYPE with no songs under it?
- DJ's on Demand (check mark here)

• How many songs can fall under one type?
- DJ's on Demand (check mark here)

Think about attributes for both of these entities.

Can you categorize the following music into playlists the same way SONGS are categorized into TYPES?

Step 3 – Understanding relationship between clients, events, and types This step may be undertaken following Section 3 Lesson 2. You learned about entities and saw how the CLIENT, EVENT and THEME entities relate. Can you think of other entities for the DJs on Demand project? You can go back to the structural business rules described in the Case Study section of this project. Step 4 – Procedural business rules This step may be undertaken following Section 4 Lesson 2.

Oracle Academy Database Design 4 Copyright © 2009, Oracle. All rights reserved.
DJs on Demand You are already familiar with the structural business rules presented in the Case Study section. Another important piece of information, important when effectively modeling a database, is represented by the procedural business rules. An example of a procedural business rule can be found in lesson 2 of section 4: Initial contact with the client must be made by the project manager. How can this constraint be incorporated in your ERD? Are there any other examples of procedural business rules?

Step 5 – Resolving many-to-many relationships This step can be taken following Section 5 Lesson 3. We have already discussed how to solve the M:M relationship between the PARTNER and EVENT entities through the JOB

ASSIGNMENT intersection entity. Include this in your ERD. How would you resolve a M:M relationship between SONG and CD? What about the relationship between SONG and EVENT? What would be suitable names for the intersection entities and what attributes would they possess? Include this step in your ERD.
Step 6 – Understanding CRUD requirements This step can be taken following Section 5 Lesson 4.

Performing a CRUD analysis on the model you have created for the DJs on Demand project is crucial. This analysis will help assess the completeness and accuracy of the data model. To do this, follow the provided business rules in the Case Study section and create a table, as shown below. Pay attention to words and phrases that affect CRUD operations (such as tracking and entering data). Identify any entity, attribute, or relationship that allows users of DJs on Demand to create, retrieve, update, or delete data. Note that all entities should have at least one create and retrieve function.

This means that the business rules for entering and viewing data in the entity should be established. For example, Table 3 provides CRUD requirements for the Oracle Academy Business Rules in the EVENT ENTITY NAME. Since multiple partners can work on an event and an event can be assigned to multiple partners, the CREATE function is used to keep track of who is working on which event. The specific business rule for this is to retrieve the DJ information from the database. Table 4 provides CRUD examples related to DJs on Demand. When a new customer is obtained, basic information like name, address, and email are recorded and an ID is assigned, which falls

under the CREATE function. To retrieve a list of songs to be played at each event, the RETRIEVE function is used. The UPDATE function allows the event manager to reserve a location, conduct site visits, note job status and dates. The DELETE function is used to remove small companies that went out of business from the current records. Step 7 involves representing mutually exclusive relationships through arcs, which can be learned in Section 7 Lesson 1.

Incorporate the exclusive OR relationship between and and in your ERD. Can you think of another example using the same method of mutually exclusive relationship?

Step 8 – Supertypes and subtypes
This step may be undertaken following Section 7 Lesson 2.
In lessons 1 and 2 of section 7, you learned about the two ways to represent supertypes and subtypes: as arcs or through recursive relationships.
Think about the advantages and drawbacks of each model.
Which way would you choose to incorporate the supertype , followed by the subtypes , , , and in your ERD? Hint: Take into account the procedural business rule about the manager described above.

Step 9 – Presenting the design to the instructor
This step can be undertaken at the end of Section 15 Lesson 4.
Create a presentation for the DJs on Demand Director, whose role will be played by your instructor.
Organize your presentation, by including:
• Statement of the problem
• Information requirements of the business clearly stated
Oracle Academy Database Design 6 Copyright © 2009, Oracle. All rights reserved.
DJs on Demand
• Assumptions and constraints you took into account
• The ERD
You will present this to your instructor, and you will be given the opportunity to present the

ERD as a communication tool, along with the business rules, to show the client that you understand their needs and that these needs are being met by your design.A suggested presentation order is as follows:
1. Introduce the group members.
2. State the addressed business issue.
3. Present and explain the ERD, ensuring it is large enough for everyone to see.
4. Summarize how the solution will meet the client's needs.
5. Present written documentation.
6. State the assumptions made in creating the solution.
7. Thank the clients for their time and exit gracefully.

Remember: For very large diagrams, consider breaking them up into smaller diagrams of functionally related entities. These smaller sub-diagrams can be used when presenting to different groups within the customer's company. Review Section 11 Lesson 1 for drawing conventions to ensure readability.

Step 10 - Making modifications and new requirements:
This step can be done at the end of Section 15 Lesson 5. Modify the ERD based on feedback received from the presentation to the DJs on Demand Director. Produce a Design Revision Document outlining the changes made since the presentation was given.

Please include the modified ERD with the Design Revision Document and submit the package to your instructor for review. Consider the potential reports that can be generated from the future system, taking into account the modifications you just made. Provide documentation on how these reports could be beneficial to the business. For instance, the staffing manager could utilize an "Events Report" to obtain a list of the partners involved in various projects. By assessing which partners are more active and perform better, the manager can make informed decisions about promotions.

Step 11 – Verification of data integrity in

the DJ database: Examine the sample data in the DJs on Demand database for these tables. Check for entity, referential, and column integrity violations. Ensure that all date columns have a proper date format and all cost columns have a numerical format.

Oracle Academy Database Design 7 Copyright © 2009, Oracle. All rights reserved.

DJs on Demand Figure 1: Sample data for DJs on Demand database

Primary Key refers to CLIENTS
id | 100 | 45 | 19

Primary Key | Foreign Key
id | 15 | 17 | 25 | 50
first_name | Antonio | Mary | Sarika | Ivan
last_name | Peters | Collins | Patel | Balazs
date | phone_num
555-1891 | 662-2275 | 383-4572 | 777-5511
description
Peter’s graduation | Collins wedding | Collins rehearsal dinner | Eiks Club annual fundraiser

Email
[email protected] |
| [email protected] |
| [email protected]

Foreign Key
800.00 |
22-Apr |
12-Mar |
|Text:
com Cocktail hour followed by dinner Garden party Black-tie affair cost client _id 100 theme_ code BB 2gs 45 TROP 400.00 45 TROP 1200.0 0 77 SIXT refers to Primary Key THEMES code CARN SIXT TROP description Carnival Sixties Tropical Step 12 – The PARTNER supertype Transform the PARTNER supertype in the DJ model, using the supertype or single-table implementation.Oracle Academy Database Design 8 Copyright © 2009, Oracle.All rights reserved.DJs on Demand 1.4.Solutions Solution Step 1 – Recognizing attributes for an entity Answer: See Table 5 Entity and attribute assignment Table 5 Entity and attribute assignment SONG v v EVENT CUSTOMER v v v v v v v v v Attributes Title Description Venue First Name Phone Number Release date Last Name Type Email address Solution Step 2 – Understanding relationship between SONG and TYPE • Must every SONG have a TYPE? Answer: Yes.The relationship

between SONG and TYPE is mandatory.• Can you have a SONG that doesn’t fall under any one TYPE? What would you do in this case?Answer: Yes, it is possible.In this case, you would create another type, because chances are there are going to be more songs classified under that type.• Must every TYPE describe or classify a SONG? Why would we want to have a TYPE with no songs under it?Answer: No, it is not necessary.But it is helpful in case other songs will be included in the database that will have this type.• How many songs can fall under one type?

Cocktail hour followed by dinner cost client <_id>100 theme_ code BB 2gs 45 TROP 400.00 45 TROP 1200.0 0 77 SIXT refers to Primary Key THEMES code CARN SIXT TROP description Carnival Sixties Tropical Step 12 – The PARTNER supertype Transform the PARTNER supertype in the DJ model, using the supertype or single-table implementation. Oracle Academy Database Design 8 Copyright © 2009, Oracle.All rights reserved.DJs on Demand 1.4.Solutions Solution Step 1 – Recognizing attributes for an entity Answer: See Table 5 Entity and attribute assignment Table 5 Entity and attribute assignment v v v v v v v v v v v Attributes Title Description Venue First Name Phone Number Release date Last Name Type Email address Solution Step 2 – Understanding relationship between SONG and TYPE • Must every SONG have a TYPE? Answer: Yes. The relationship between SONG and TYPE is mandatory. • Can you have a SONG that doesn’t fall under any one TYPE? What would you do in this case? Answer: Yes,

it is possible. In this case, you would create another type, because chances are there are going to be more songs classified under that type. • Must every TYPE describe or classify a SONG? Why would we want to have a TYPE with no songs under it? Answer: No, it is not necessary. But it is helpful in case other songs will be included in the database that will have this type. • How many songs can fall under one type?The diagram and business scenario suggest that a TYPE can be employed to classify multiple songs, or as many as necessary for the database. Consider attributes for both of these entities.

Answers will vary, but for SONG, some attributes (actually used in the ERD) are id, title, duration, and artist. For TYPE, the attributes are code and description.

Examples of playlists:
- 90's music
- My top rated
- Recently added
- Recently played
- Oracle Academy Database Design

Copyright © 2009, Oracle. All rights reserved.

DJs on Demand:
- Party shuffle
- Rock (might include indie, alternative, hard rock)

Students should now categorize the songs belonging to these playlists or others.

Solution Step 3 - The relationship between clients, events, and types in the DJs on Demand project can be understood through the following entities: PARTNER, CLIENT, EVENT, JOB ASSIGNMENT, PRIVATE HOME, PUBLIC SPACE, THEME, TYPE, PLAY LIST ITEM, SONG, PACKAGE, TRACK LISTING, and CD.
Solution Step 4 - Procedural business rules for implementing a computer program include a system where partners can access and update events. The program should only allow the project manager to add new events and assign partners to them. After the assignment is made, the system should notify the event manager and DJ

via email with event details and customer contact information. Other procedural business rules include: only the project manager can modify event details after client requests, only the event manager can arrange event venues, and all business advertisements must be approved by the manager.
Solution Step 5 - To resolve many-to-many relationships between SONG and CD, an intersection entity called TRACK LISTING with an attribute called "* track" can be used.

The intersection between PARTNER and EVENT is JOB ASSIGNMENT, which has attributes including status and #date. Students should include these entities in their preliminary ERD. In terms of CRUD requirements, the project manager creates initial contact with the client for event discussions. The event planner then contacts the client for specific details such as location, catering, decorations, etc. The DJ communicates with the client regarding their music preferences. The client can also request additional songs as updates are made. Since multiple partners can work on an event and multiple events can be assigned to partners, it is important to keep track of who works on each event. A log is kept to record the actions of event planners and DJs and when they occurred. New types, such as rap music, can be added when needed. Event themes are continuously added. Students should incorporate these new relationships into their preliminary ERD and understand why they are necessary.In response to being asked for another example using the same method of mutually exclusive relationships, answers may differ. However, it is beneficial to introduce them to various entities related to the PARTNER entity, such as EVENT PLANNER, DJ, MANAGER, and OTHER. In Step 8 of the solution, supertypes and subtypes are

discussed as advantages of recursive relationships in an ERD. Supertypes/subtypes are commonly employed to represent classifications or types of entities, adding a hierarchical aspect to the diagram.

Arcs are utilized to depict relationships between entities that are mutually exclusive. A supertype/subtype construct is not suitable for an M:1 relationship. Additionally, the use of arcs is recommended when the exclusive relationships between entities hold greater significance for the business. The arc visually communicates to the reader of the diagram that, for each instance, only one "feature" of the main entity will possess the relationship with the main entity. The entities EVENT PLANNER, DJ, and MANAGER have distinct sets of attributes, thus necessitating their modeling as separate entities.

When the supertype/subtype relationship is represented as an arc, the relationships within the arc are mandatory 1:1 relationships. This hierarchical structure is more explicit and easier for most people to understand, as it closely resembles an organizational chart. Each entity within this structure can have its own mandatory attributes and relationships if required by the business, ensuring that the data model accurately reflects the business rules. In contrast, recursive relationships involve using only one entity, making them simpler.

Your diagram will be simpler. However, it will be less detailed - you cannot include mandatory attributes or relationships unless they are mandatory for every instance of the entity. Oracle Academy Database Design 11 Copyright © 2009, Oracle. All rights reserved.
DJs on Demand More: If the types of relationships frequently change, a recursive relationship is easier to manage. If the structure is constant, then a more explicit hierarchy may be preferable. A structure that remains relatively unchanged would be a building with suites

and floors and rooms.

This document argues that using an explicit hierarchy is beneficial in certain cases, while a company that reorganizes frequently may be better suited for a recursive relationship. The use of subtypes and the recursive relationship is preferred in the context of the procedural business rule that requires initial client contact to be made by the project manager and for managers to supervise other partners. The ERD enables managers to effectively oversee other managers.

Solution Step 9 - Presenting the design to the instructor:

Each presentation must include the following:

• Statement of the Problem
• Statement of the Proposed Solution
• The information requirements of the business/organization clearly stated
• The business rules as they apply to the information requirements of the company/organization stated as single sentences
• The assumptions and constraints that had to be considered clearly stated

Solution Step 10 - Making modifications and new requirements:

Examples of reports:

• List of the locations of private homes used for parties so that the business knows where to advertise more
• List of the most interested clients, so that they can receive discounts
• List of the most played songs so that the DJs can make better recommendations/improve their playlists

Solution Step 11 - Checking data integrity in the DJ database:

Table 6 Data integrity check CLIENTS table

Oracle Academy Entity integrity: ID for Sarika Patel should not be null.

Column integrity: Cost for Collins wedding (event id = 17) is not numeric.
Column integrity: Date for Collins rehearsal dinner (event id = 25)

is not a valid date format.
Referential integrity: There is no client 77 in the CLIENTS table (event id = 50).There is no theme corresponding to "BB" in the THEMES table (event id =15). Additionally, in the PARTNER supertype, it is necessary to include a discriminator column named "pnr_type" with allowable values of EPR, DJ, and MNR. To ensure referential integrity, a check constraint should be implemented to validate the values of the discriminator column. This constraint should check for specific conditions based on the values of the discriminator column. The foreign-key column "pnr_id" in the PARTNERS table refers to the recursive relationship in PARTNER and represents the ID of the partner's manager. The Global Fast Foods project involves a small fast-food restaurant seeking to improve tracking of staff, shifts, orders, and food items.The director is requesting reports that include the busiest times of the day, the hardest worker, the employee with the most orders, the most popular items, and the customers who frequently visit our Global Fast Foods restaurant. The difficulty scale for this project is 2, and it is intended for the application of basic concepts from the Database Design course. To start this project, please refer to Section 3 Lesson 3. Similar to other projects, this one involves a series of practice steps, with each step depending on the outcomes of the previous step. It is recommended to complete each step at the end of a specific lesson in the course.

Like the DJs on Demand project, Global Fast Foods also has predefined business rules. Upon completion of this project, students will have revised ERDs that emphasize key data modeling concepts, including supertypes/subtypes, relationship

types, 1NF, arcs, hierarchies ; recursive relationships, resolving many-to-many relationships, and normalization. Step 9 of the DJs on Demand project is Modeling historical data, which enables students to apply their understanding of intersection entities and barred relationships.

This project will allow students to develop their skills in drawing conventions for readability in an ERD. The ability to easily read the diagram is essential for a successful presentation. The project includes steps for students to create a presentation of their data modeling solution. They will then modify it according to feedback from the instructor, who will be playing the role of the Global Fast Foods' director.

The lessons that reference this project are:
- Section 3 Lesson 3
- Section 4 Lesson 1
- Section 5 Lessons 1, 2, and 3
- Section 6 Lessons 2 and 4
- Section 7 Lessons 1, 2, and 3

Please note that if students skip any of these lessons, they may not be able to successfully complete the corresponding practice step or any later steps in the project.

On a scale of 1 to 5, the difficulty of this project is rated as 2.5. Instructors should choose to complete this project in class when students would benefit from more practice in applying basic concepts in data modeling, such as Oracle Academy Database Design.

The Global Fast Foods project includes elements such as supertypes/subtypes, relationship types, 1NF, arcs, hierarchies, and recursive relationships.

The final ERD can be found in the solutions section at the end of t