In this assignment, you will identify three companies and think critically about how they use databases to enable their business. After completing this assignment, you should be able to identify the types of data a company might be interested in storing, and how it is related to other data. You should also begin to see complexities in identifying how data is stored.
1. Choose three of the categories below and identify one specific company, organization, or service from each.
|
Banking/Finance
|
Retail
|
Education
|
Entertainment
|
|
Examples
|
Banks
Investment firms
Insurance
|
Physical stores
Online stores
Convenience stores
|
Grade schools
Colleges
Training seminars
Online learning
|
TV, music, and movies Streaming services
Television networks
Gaming
|
|
Food Services
|
Healthcare
|
Service
|
Transportation
|
|
Examples
|
Restaurants
Groceries
Farming
Food production
|
Doctors, dentists, etc.
Hospitals/urgent care
Pharmacies
|
Government svcs.
Postal service
Construction
Lawn care
|
Auto manufacturing
Taxi services
Airlines
Shipping
|
2. For each company, identify two entity types they need to keep track of.
3. For each type of data, identify five attributes for each entity type you identified in #2
4. Create a data dictionary showing the column names, table names, data type, and length for the entities in #2. See page 15 in your book or slide 24 in the day 3 slides for an example.
5.Draw an ERD showing the relationship between the two entities. Include the cardinality and participationconstraints. You do not need to show the attributes in the ERD. It is easiest to make these drawings in software such as PowerPoint or Visio.
6. Write out the business rules that are presented in your ERD. The business rules should describe the entities, their attributes, domain constraints (optional, but should be included if needed), cardinality, and participation constraints. You may either write this in long form (for example, Day 2 slide 61 or Day 4 slide 27) or use bullet points. This must include participation and cardinality
Name your file Lastname_Firstname_A1.docx (i.e., Knighton_Bill_A1.docx) and upload to Blackboard by 10:00 PM CST on Monday, February 6
Notes
1. Do not pick multiple companies from one category, and do not use the same company for multiple categories.
2. Do not use the same (or very similar) entities for every company. For example, do not just create a relationship of "Customers purchase products" for every company. Not cool and won't count.
3. Use attributes that companies would realistically have access to. For example, most retailers would not know (or need to know) a customer's weight.
4. Some companies fit in to multiple categories - i.e., Walgreens could be considered both "retail" and "healthcare". Choose the category that matches the aspect you are describing.
5. You may choose a "service" rather than a company - for example, Amazon Prime Video is a service owned by Amazon, but would be an acceptable service.
6. I anticipate each company will take approximately one page - so your final deliverable should be around 3 pages.
7. If you have read ahead or have database experience and want to demonstrate your advanced knowledge that is fine - however, artificial keys and foreign keys do not count as attributes for this exercise.
8. Please put some thought into the companies you select and do not just go with obvious/easy choices. For example, "Amazon" is likely the first e-commerce company you thought of - and I don't want everyone to pick Amazon. This is both very boring to grade and does not encourage you to think critically.
You don't need to pick a company that is super obscure, but try to pick something interesting. There are plenty of well-known companies to go around.
Assignment 1 Example
1. Company: Mark's Horse Hostel (Service)
2. Entity types: Horses, Customers
3. Attributes:
Horses (Name, Weight, Gender, Color, Breed)
Customers (Name, Phone, Address, Email, Balance)
4. Data Dictionary
|
Column
|
Table
|
Data Type
|
Length
|
|
Name
|
Horses
|
Text
|
30
|
|
Weight
|
Horses
|
Number
|
16
|
|
Gender
|
Horses
|
Text
|
1
|
|
Color
|
Horses
|
Text
|
10
|
|
Breed
|
Horses
|
Text
|
30
|
|
Name
|
Customers
|
Text
|
30
|
|
Phone
|
Customers
|
Text
|
12
|
|
Address
|
Customers
|
Text
|
50
|
|
Email
|
Customers
|
Text
|
50
|
|
Balance
|
Customers
|
Number
|
16
|
5. ERD
6. Business rules
Mark's Horse Hostel is a business that cares for horses owned by customers. All customers must own at least one horse, but some customers have many horses. All horses kept at Mark's Horse Hostel are owned by exactly one customer. Due to size limitations of the stables, all horses must weigh less than 2,000 pounds. A horse's color may be specified as Black, White, Dark Brown, Light Brown, Red, Yellow, or Other. Customers are not required to provide their email address.
Or you can use bullets:
- All customers have at least one horse
- A customer may have multiple horses
- A horse is owned by exactly one customer
- All horses must weigh less than 2,000 pounds
- Horse color is classified as Black, White, Dark Brown, Light Brown, Red, Yellow, or Other
- Customer's email address is optional
Notes about the assignment:
You may noticed that I have not included attributes such as "CustomerID" or "HorseID" that would make for good (artificial) primary keys. If you want to include something like that it is fine, but do not count that as one of your five attributes - list things that are meaningful to describing the entity instances. The point of this assignment is to critically think about what types of data companies need to store to do their job. We will deal with key values and their relationships more in future assignments.
For the length of text attributes, put what you think a reasonable number of characters would be. For example, for gender we can just store "M" or "F". For Number values, the length is the number of bits - so 16 means 2^16 = 65,536 (any number between 0 and 65,535 will fit). You can just use 16 for the length of numbers, unless you want to be more precise in your answer.
I provided some domain constraints in the business rules - I would encourage you to do the same where it is appropriate (You don't need a constraint for every attribute, but if there are places a domain constraint would be beneficial you should mention it).
You can make the ERD directly in word (using the shapes on the "Insert" tab), or make it in PowerPoint, Visio, etc. using the drawing tools. There are several ways to save your drawings in PowerPoint as an image you can insert into the word document. One approach is to take a screenshot:
Windows: https://windows.microsoft.com/en-us/windows-8/use-snipping-tool-capture-screen-shot