Database Model Conventions

Instructions

This contains the instructions for the database you need to model for your project.

General Instructions

You will receive a unique random database specification (as a random question). You must follow these instructions for your received database specification.

You must create a database design in MySQL Workbench and create a professional report presenting your design. Based on your provided specification (different for each student) you must create a simple database design.

This is a CLOSED BOOK assignment, and you are not allowed to use any external information to solve it. Be sure that you use the specification that was assigned to you. Solving a different version of the assignment will result in a grade of zero for this assignment and may result in honor committee referral.

You will continue the design in Part 2

Database Model Conventions

You must follow these conventions when you perform your homework. Failing to follow the conventions will be penalized per each mistake and may also constitute evidence of cheating.

• Entity names: singular nouns (or noun phrase) written in CAPITAL LETTERS, using _ instead of space, must end with a two letter suffix with your first name and last name initials (e.g. if your name is John Doe, an entity name will be PERSON_JD)
• Attribute names: singular nouns (or noun phrase) written in CAPITAL LETTERS, using _ instead of space, must end with a two-letter suffix with your first name and last name initials (e.g. if your name is John Doe, an attribute name will be FIRST_NAME_JD)
• Relationship names: third person verbs (or verb phrase) written in lower case letters, using spaces. Do not use “has” or other generic names. Use a name that clarifies the meaning (even if contains entity names)
• Additional conventions might be specified by your instructor (e.g. prefix the attribute names with the entity name or abbreviation)

Report Overview (PDF)

Your report must contain the following sections, which are described below:

• Cover Page
• Conceptual Design
• Entities Analysis
• Relationship Analysis
• Entity Relationship Diagram

Cover Page

The report must start with a professionally designed cover page including all the following information:

• Your name
• Your course and section
• A suggestive title for your report
• A short original description of the database (at least two sentences)
• Relevant graphics or picture (must be a suggestive picture for the content of your report)

Conceptual Analysis

Before you start a detailed analysis you must figure out which are the main elements in your design

• Identify the main entities involved
• Identify the main relationship involved.
• Identify the relationship type and explain how you obtained it
• For many to many relationships identify the bridge, and the two other relationships needed.

Entities Analysis

You must analyze the entities identified including the bridges:

• Entity name
• Entity description (a short paragraph)
• An attribute table, with one row for each attribute and with the following columns:

• • Attribute name
  • Attribute domain and size, e.g. CHAR(10), INT, VARCHAR(30)
  • Simple/Composite (without explanations)
  • Single/Multi Value (without explanations)
  • Required/Optional (without explanations)
  • Unique/Duplicated (without explanations)
• The selected primary key and its type (simple/composite/surrogate).
• Any foreign key specifying the referenced primary key and entity.

Relationship Analysis

• Relationship name: PARENT_ENTITY relationship name CHILD_ENTITY (the name is a verb or verb phrase 3rd singular)
• Relationship type with explanation
• Relationship strength with explanation
• Entities participation in the relationship with explanations
• Any special cardinality as needed, or state there is no special cardinality.
• Foreign key used for representation.

Entity Relationship Diagram

After you finished the entity analysis you must draw the ERD in MySQL Workbench and include in your report the following screenshot(s):

• A signed screenshot (or more if needed) with the ERD done in MySQL Workbench (to sign a screenshot you write your name in a text object visible in the screenshot; crop the image to show only the ERD and the text object)
• A screenshot for each entity (table) description in MySQL Workbench (crop the image to show the entity name and attribute table). Be sure you correctly identify the entity name, attributes names, attributes domains, attributes characteristics (e.g. required/optional and unique/duplicated) and the primary key.

What to submit

You must submit the following files:

• A PDF file containing the report for your database (as described above) as the answer to first question
• A MWB file containing the database model done in MySQL Workbench as the answer to second question

Submission instructions:

• You must submit your assignment answering the following questions with a file.
• You must name the files: P1_lastname_firstname.pdf and P1_lastname_firstname.mwb
• You must not have the files open in a program when you submit them, because they might not be submitted correctly.
• Double check the files before submission. You are not allowed to resubmit your assignment. Do not ask for resubmission because cannot be granted. Allowing one student to resubmit will imply to allow all the students to resubmit from all the sections (in order to be fair). This cannot be handled.

ZERO GRADE:

You will receive a grade of zero in ANY of the following situations:

• The report was done based on instructions from a previous semester.
• The report was done based on specifications from a previous semester.
• Your ERD screenshot(s) are not signed, or signed correctly (a textbox added in MySQL Workbench with your name)
• The names of your entity do not follow the name prefix/suffix rule described above.
• The names of your attributes do not follow the name prefix/suffix rule described above.

Happy Learning (this is a fictional scenario)

A private kindergarten, Happy Learning, wants to build a database to manage their operations. You were assigned to create an initial design based on the following customer description of their operations.

There are 3 age levels in the kindergarten (turtles, foxes and monkeys). At each level there 3 classes (red, blue and green). For a class we keep a code based on the initial of group color and level (e.g. RT for red turtles, GF for green foxes). Each class has a room number and a phone number (with the format (999) 9999999). Also, each class has a maximum capacity (between 25 and 35). For some classes we will keep brief directions to the class location in the building (less than 160 characters).

For a student we will keep the name (first, last, middle each less than 15 characters), any nickname (less than 10 characters), date of birth, address, and the class in which the student is currently enrolled. We will process students based on their first and last name, or nickname, if present. We will process the address based on the city (less than 15 characters), zipcode-5 and street (less than 35 characters) to identify recruiting areas.

For each student we will keep information about one or more parents (or caretakers). A parent may have several students enrolled in the kindergarted. For a parent (or caretaker) we will keep the name (first, last, middle), home phone number, mobile phone number, work phone number, (same format as above), the personal email (less than 30 characters) and relationship to the child (e.g. father, mother, grandfather). At least one type of phone is required, but some parents may not provide all types of phone numbers. Some parents do not provide emails. We will process parents based on their last name.

Each class has one or more teachers assigned. A teacher is assigned to one or more classes. For a teacher we keep the name (first, middle, last), home phone number, work email, personal email, the graduated college and their highes degree (e.g. BS, MS, MA, … – a maximum five characters code) and the area of the degree (at most 25 characters). All the teachers must have at least bachelor degree. We will process teachers based on their first and last name, phone, email. The format for the name, phone, email as described above.