In this work, the database for the business case described below must be designed and implemented. Once this is done, it is necessary to record a video presenting the conclusions of the work.

Case:

The Santiago Zoo needs to implement a computer application to manage and organize information on the species that live there, the employees who work there (caretakers and guides), and the different guided tours offered to visitors.

For all species that arrive at the zoo, a card must be created that records the date of entry, general condition and weight. On the other hand, it is required to consider the name of the species, its scientific name, average lifespan and a general description of each of them. A unique numerical identifier is created for each species. The sheet has information on only one species exclusively. Species can live in different natural habitats and one habitat can be occupied by different species. Habitats are defined by a unique identifier, their name, the predominant climate and vegetation type. As well as the continent where it is predominantly located. The continents are numbered from 1 to 5. The species are found in a specific sector of the zoo, but many species can coexist in one sector. Each sector of the zoo is defined by a unique identifier, the name and the extent of the land it occupies. There may be empty areas. After the species is registered and its file is created, it is obligatorily assigned to a specific keeper.

Keepers are defined by their full name, address, telephone number and date of entry into the zoo. One keeper can be in charge of several species and, in turn, one species can be cared for by several keepers. 

The zoo offers tours that correspond to guided tours led by an advisor. The defined tours cover different sectors of the park. The information of interest for the tour is: tour code, start time, duration and length of the tour, the maximum number of visitors that can be registered. Each tour describes the areas of the zoo you visit. Every tour visits at least one area of the zoo. Visitors can sign up for as many tours as they want, they are free. People who visit the zoo and want to register for a tour must register their details and those of their visit. About the visits you need to know: the correlative of the visit, the day of the visit, the reason of most interest for your visit, optionally. A person can record many visits to the zoo on different days. You need to know the personal information of the guide who leads the different tours. The visitor data corresponds to the name, ID, full address, commune, telephone, email and date of birth.

A. Indications for the development of the work.  
 

To develop the design and implementation work, we suggest the following steps:

Step 1. Identify the entities and relationships of the proposed case to build the entity relationship (MER) model of the conceptual design of the database.

Step 2. Normalize the entity relationship model by applying the respective transformation rules for the construction of the normalized or canonical entity relationship model (normalized MER).

Step 3. Transform the normalized entity relationship model (normalized MER) to a relational model that corresponds to the logical design of the database.

Step 4. Transform the relational model to the physical database tables (physical design process). For this, the DDL SQL script must be implemented that allows the construction of the database.

Step 5. Create the complete project using Oracle Data Modeler for the stages that correspond to conceptual design, logical design and generation of the DDL that corresponds to the physical design of the database. (Remember to save the complete folder for sending and subsequent teacher review).

Step 6. Export the MER, normalized MER and relational model to images in PNG format. They must be included in the project folder

Step 7. Export the DDL SQL script into a file with sql extension. It should be included in the project folder.