Assignment 1: Who uses databases?

WHO USES DATABASES? .

MIS 3376, Spring 2018 (Grimes)

Assignment 1

Assignment 1: Who uses databases?

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 for an example.

5. Draw an ERD showing the relationship between the two entities. Include the cardinality and participation constraints. 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. For each entity, provide 3-7 instances using fictional data. Create and populate a table for each entity that includes sample values that demonstrate the participation and cardinality.

7. 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 or use bullet points.

Name your file Lastname_Firstname_A1.docx (i.e., Grimes_Mark_A1.docx) and upload to Blackboard by 10:00 PM CST on Monday, February 5

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 1.5-2 pages – so your final deliverable should be around 5-6 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 #1: Mark’s Horse Hostel (Service)

2. Entity types: Horses, Customers

3. Attributes: Horses (Name, Weight, Gender, Color, Owner Name) 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
Owner Name 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. Tables

Horses
Name Weight Gender Color Owner Name
Amy 700 F Brown Norman Johnson
Dave 1200 M Brown Mark Grimes
Ed 1000 M Black Richard Scamell
Sarah 1500 F Red Mark Grimes
Tom 1100 M Black Richard Scamell
Customers
Name Phone Address Email Balance
Mark Grimes 281-777-6666 40 Wallaby Way gmgrimes@uh.edu 2000
Norman Johnson 281-666-7777 41 Wallaby Way 3000
Richard Scamell 281-555-9999 42 Wallaby Way rscamell@uh.edu 2000

7. 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 as an image you can insert into the word document, one easy way is to take a screenshot (Google or ask a friend if you need help with this).

Please follow and like us: