Dog Registration Database
Objective
- Track data for dogs, owners and violations
- Create an ER diagram
- Create searches and output using relational algebra
Background
The Yorkie’s Dominance: We Analyzed Every Dog Registration in New York, New York Times, February 10, 2018.Database Design
Your design must track the at least following categories- Dog owner information including name, address and email. Dog owners can own many dogs.
- Dog information including dog name, breed, gender, weight, age, photos and owner. Track current and previous dog owners. A dog can have many photos.
- Dog owners can receive tickets with violations including dog, owner, violation type, fine, date of violation and current status of violation. One ticket can include many violations.
Identify and create the following in your database design
- Entity Relationship (ER) diagram
- Relations
- Degree
- Primary and foreign keys
- Domains
- Relationship between entities
- Relationship type
- Attributes
- Cardinality
- Tuples
- Your ER diagram will include all attributes type including single value, multi value, composite and derived.
- Convert the E-R diagram to relations in the format of: relation(attribute1, attribute2, attribute3). For instance, book(ISBN, title, author, price).
You must include at least six relations and at least three attributes for each relation.
Include at least three multi-value attributes in your design.
Relational Algebra
- Generate relational algebra to answer the queries below.
- Use standard notation and relational algebra terminology.
- You may need to modify you E-R design to answer the questions below.
- Replace the underlined terms with your own values and maintain the intent of the search. For instance: replace London College with another neighborhood and last
- Create descriptive attribute labels.
- Identify dogs without violations in the last year. Display the owner name, dog name, breed and email.
- Identify neighborhoods without registered pit bulls today. Display the neighborhood.
- Identify owners without any registered dogs today. Display the owner name and email.
- Identify owners who live near London College with registered dogs today. Display the owner name and email.
- Identify pictures of female poodles less than five years old. Display the dog name, age and photo(s).
- Identify dogs owned by Bo Li with fines in the last year. Display the owner name, dog name, violation, date of violation and fine.
- Identify the number of male dogs by dog name. Display two columns and one row for each dog name. The two output columns are dog name and number of dogs with that name. Use an aggregate function and grouping operation to answer this question.
- Identify the number of poodles by neighborhood. Display two columns and one row for each neighborhood. The two output columns are zip code and number of poodles in that zip code. Use an aggregate function and grouping operation to answer this question.
- Identify the number and total fines by owner. Display three columns and one row for each owner. The three columns are owner, number of violations and total dollar amount of fines. Use an aggregate function and grouping operation to answer this question.
- Identify the number of registered female poodles in the database today. Display one row with the number of registered dogs.
Formatting
- Your project must be typed.
- The E-R design must be similar to your relational algebra, including attribute names and attribute types.
- Your project must include the question and relational algebra operations to answer the question
- Use appropriate terminology.
- Diagrams must be illustrated using software such as Microsoft Word, Microsoft Visio or LucidChart. If you manually create diagrams, they must be neat and clear.