Project Phase-2 Team-9

docx

School

New Jersey Institute Of Technology *

*We aren’t endorsed by this school

Course

631

Subject

Computer Science

Date

Jan 9, 2024

Type

docx

Pages

Uploaded by AgentProtonQuetzal39

Data Management Systems Design (CS631) By Prof. Michael Renda CS631-003 TEAM 9 Project Name: Turtleback Zoo Online Application Project PHASE II Group Members Naga Datha Saikiran Battula (nb547) Sai Teja Nagapuri (sn872) Sruthi Pushdapu (sp3287)

Outline: Develop a structured relational schema based on the entities, attributes, and relationships identified in the EER diagram from Phase 1 of the project. Also, create a well-defined structure for the database that aligns with relational database principles. This structure facilitates efficient data storage, retrieval, and management. Added Entity: Visitor: Visitor ID (Primary key), DOB, Age, first_name, Middle_name, Last_name, Contact_Info. Added Relationships: 1. The interacts_with relationship between Visitor Entity and Employee Entity which is Many-to- Many relationship with min-max as (0, N) on both sides. 2. Observes relationship between Visitor and Animal which is Many-to-Many relation with min-max as (0, N) on both sides. Modified EER diagram: (source: Prof. Michael Renda)

Relational Schema:

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

EER diagram Overview: The relational schema for the Turtleback Zoo Online Application database comprises the following tables: Visitor, Interacts_with, Employee, Hourly Rate, CaresFor (Vet), observes, Animal, Building, Enclosure, Species, ParticipatesIn, Revenue Types, Revenue Events, Animal Show (AS), Concession (Conc), Zoo Admission (ZA). All relationships between entities are represented within the tables, adhering to the translations from the Extended Entity-Relationship (EER) diagram to the relational schema. Arrows indicate foreign keys pointing from one table to the referencing table with the primary key. Primary keys and partial keys are explicitly denoted with underline for clarity. Description: Translation (Mapping): Step-1: Mapping of Regular Entity Types  Visitor: VisitorID ( Primary key ), DOB, Contact_info, First_name, Middle_name, Last_name.  Employee: ID ( Primary key ), StartDate, JobType, FirstName, Minit, LastName, Street, City, State, Zip, SuperID ( Foreign Key -> Employee.ID) Hourly_rateID ( Foreign Key -> HourlyRate.ID)  Hourly Rate: ID ( Primary key ), Rate.  Animal: ID ( Primary key ), Status, BirthYear, BuildingID ( Foreign Key -> Building.ID) EnclosureID ( Foreign Key -> Enclosure.ID) SpeciesID ( Foreign Key -> Species.ID)  Species: ID ( Primary Key ), Name, FoodCost.  Building: ID ( Primary Key ), Name, Type.  Revenue Types: ID ( Primary Key ), Name, Type, BuildingID ( Foreign Key -> Building.ID) Step-2: Mapping of Weak Entity Types Identifying entity for Enclosure is Building having ID as primary key. The identifying entity for Revenue Events is Revenue Types having ID as primary key.  Enclosure: BuildingID ( Primary Key ), ID ( Partial Key ), SqFT.  Revenue Events: ID ( Primary Key ), Datetime ( Partial Key ), Revenue, TicketsSold. Step-3: Mapping of Binary 1:1 Relation Types There are no 1: 1 Relation Types in the EER diagram. Step-4: Mapping of Binary 1: N Relationship Types  Supervises (1: N) from Employee to Employee which is represented with foreign key of SuperID in Employee table.

 Earns (1: N) from Hourly Rate to Employee: Foreign key of Hourly_rateID in Employee table.  WorksAt (1: N) from Concession to Customer Service: Foreign key of ConcessionRevenueTypeID in Customer Service table.  WorksAt (1: N) from Zoo Admission to Ticket Seller: Foreign Key of ZooAdmissionRevenueTypeID in Ticket Seller table.  Memberof (1: N) from Species to Animal: Foreign key of SpeciesID in Animal table.  LivesIn (1: N) from Enclosure to Animal: Foreign Key of EnclosureID and BuildingID in Animal table.  Part of (1: N) from Building to Enclosure: Foreign Key BuildingID in Enclosure table.  Hosts (1: N) from Building to Revenue Types: Foreign Key BuildingID in Revenue Types table.  Occurs (1: N) from Revenue Types to Revenue Events: Foreign Key RevenueTypeID in Revenue Events Table. Step-5: Mapping of Binary M: N Relationship Types  Interacts_with (M: N) between Visitor and Employee: New table Interacts_with with foreign key of VisitorID and EmployeeID.  Observes (M: N) between Visitor and Animal: New table Observes with Foreign Key of VisitorID and AnimalID.  ParticipatesIn (M: N) between Species and Animal Show: New table ParticipatesIn with Foreign Key of AnimalShowRevenueID and SpeciesID.  CaresFor (M: N) between Veterinarian and Species, and Animal Care Specialist and Species: New table CaresFor with Foreign Key of Employee_ID and SpeciesID. Step-6: Mapping of Multivalued attributes There are no Multivalued Attributes in the EER diagram. Step-7: Mapping of N-ary Relationship Types There are no N-ary Relationships in EER diagram. Step-8: Options for Mapping Specialization or Generalization  Employee entity have Veterinarian, Animal Care Specialist, Maintenance, Customer Service and Ticket Seller as Specializations. For these the subtypes are disjoint and represented in the employee table with jobtype.  Revenue Types entity have Animal Show, Concession, and Zoo Admission as Specializations. Each subtype is represented in a new relational table with RevenueTypeID as primary key. Step-9: Mapping of Union Types There are no Union Types in EER diagram. Other keys: Partial Key: - EnclosureID: EnclosureID is the partial key in Enclosure Entity. - DateTime: DateTime is the partial Key in Revenue Events.

Additional Constraints:  DOB attribute should only be represented as ‘MM-DD-YYYY’ format in string.  Zip attribute in Employee should be an integer with 5 digits.  DateTime attribute in Revenue Events should be represented as ‘MM-DD-YYYY hh:mm:ss’ format in string.  Visitors can only interact with employees who exist in the database.  Employees can only be assigned to buildings that exist in the database.  Animals are associated with valid species in the database.  Different Revenue Events occur and contribute to Revenue Types. Difficulties: During the EER to Relational Mapping phase, there are several challenges that can be faced. Some of these challenges include:  Determining the appropriate mapping rules for the EER model.  Ensuring that the relational schema is in a desirable normal form.  Ensuring that the relational schema is consistent with the EER model.  Ensuring that the relational schema can support the desired queries. These challenges can be addressed by:  Using a systematic approach to mapping the EER model to the relational schema.  Using a set of well-defined mapping rules.  Using a normalization process to ensure that the resulting relational schema is in a desirable normal form.  Using a set of consistency rules to ensure that the resulting relational schema is consistent with the EER model.  Using a set of query support rules to ensure that the resulting relational schema can support the desired queries. Drive link

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

Related Documents

Quiz 2.docx

Midterm Exam Note.docx

Quiz 1.docx

JT5kxst-SgmbtcUce6gj4g_e5493284ec9042f59f79b5b3bf61f3f1_CSE-535_Team-Assignment_Idea-Paper_Overview.

Assignment 4 .docx

homework5.pdf

dsa hw 3.docx

SAM_EX19_MOS_4a_JustinAujla_Report_1.xlsx

Virtualization.docx

CS 3307 Unit 3.docx

Written Assignment Unit 3.docx

Unit2_WA_231126.docx

Recommended textbooks for you

Database Systems: Design, Implementation, & Manag...

Computer Science

ISBN:9781305627482

Author:Carlos Coronel, Steven Morris

Publisher:Cengage Learning

A Guide to SQL

Computer Science

ISBN:9781111527273

Author:Philip J. Pratt

Publisher:Course Technology Ptr

Principles of Information Systems (MindTap Course...

Computer Science

ISBN:9781305971776

Author:Ralph Stair, George Reynolds

Publisher:Cengage Learning

Fundamentals of Information Systems

Computer Science

ISBN:9781337097536

Author:Ralph Stair, George Reynolds

Publisher:Cengage Learning

Oracle 12c: SQL

Computer Science

ISBN:9781305251038

Author:Joan Casteel

Publisher:Cengage Learning

Principles of Information Systems (MindTap Course...

Computer Science

ISBN:9781285867168

Author:Ralph Stair, George Reynolds

Publisher:Cengage Learning

SEE MORE TEXTBOOKS

Recommended textbooks for you

Database Systems: Design, Implementation, & Manag...
Computer Science
ISBN:9781305627482
Author:Carlos Coronel, Steven Morris
Publisher:Cengage Learning
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr
Principles of Information Systems (MindTap Course...
Computer Science
ISBN:9781305971776
Author:Ralph Stair, George Reynolds
Publisher:Cengage Learning
Fundamentals of Information Systems
Computer Science
ISBN:9781337097536
Author:Ralph Stair, George Reynolds
Publisher:Cengage Learning
Oracle 12c: SQL
Computer Science
ISBN:9781305251038
Author:Joan Casteel
Publisher:Cengage Learning
Principles of Information Systems (MindTap Course...
Computer Science
ISBN:9781285867168
Author:Ralph Stair, George Reynolds
Publisher:Cengage Learning