Homework 1: Database Schema Design

The goal of this homework is to practice designing a database schema.

Instructions

You are welcome to work with a partner.

  1. Either use the website you analyzed in Lab 1, or choose another database application with which you are familiar.
  2. Design a schema and populate it with example data. Your schema should include:
    • At least four tables. Use the notation shown in the example below.
    • Each table should include at least four rows (records) of example data.
  3. List at least four database constraints which should constrain data stored by the schema.
  4. List three users of your database. For each user:
    • In detail, describe what data should be visible to them.
    • In detail, describe what data (if any) they should have permission to modify.

Hint: Section 1.6.8 describes examples of constraints you might include.

Example

  1. Database application: Facebook
  2. Schema: (See tables below)
  3. Contraints:
    • Each Email in the Users table must be unique
    • Each Email in the Users table must be a valid email address
    • Each ID in the Users table must be unique
    • Each Birthday in the Users table must be a valid date
    • Each User ID 1 and User ID 2 in the Friendship table must correspond to values in the Users table
  4. Users of the database:
    • Personal users:
      • Can view and edit all attributes of their record in the Users table
      • Can view the Name attribute of all records in the Users table
      • Can view all Users table attributes of all their friends
      • Can view and edit their own Status Updates
      • Can view all Organizations records
      • Can view and edit their own Likes
    • Organizational users:
      • Can view and edit all attributes of their record in the Organizations table
      • Can view the number of Likes for their organization
    • Facebook admins:
      • Can view and edit all attributes of all tables

Users

ID Name Email Password Birthday Phone Number Relationship
111 Peter Story PeStory@clarku.edu — Jan 1, 1990 111-222-3333 Married
112 Abe Story AbStory@clarku.edu — Feb 1, 1980 111-222-3334 Single
113 Ben Story BeStory@clarku.edu — Mar 1, 1970 111-222-3335 Married
114 Cathy Story CaStory@clarku.edu — Apr 1, 1960 111-222-3336 Unknown

Friendship

User ID 1 User ID 2
111 112
111 113
111 114
112 113

Status Updates

Timestamp User ID Text
2023-11-29 10:57:01 111 The CMACD building is great!
2023-11-29 11:38:17 111 Why is it so cold outside?
2023-11-29 11:46:29 112 Looking forward to summer!
2023-11-29 14:38:17 113 I’m quitting social media!

Organizations

Org ID Name Address
1111 Facebook Boston 100 Binney St Cambridge, MA 02142
2222 Apple 1 Infinite Loop Cupertino, CA 95014
3333 Clark University 950 Main St Worcester, MA 01610
4444 Worcester DPW 20 East Worcester St Worcester, MA 01604

Likes

User ID Org ID
111 1111
112 2222
113 3333
113 4444

Submit

Upload a PDF with your responses.