ITM 111: Introduction to Databases

W02 Team Activity: Database Design -Case Study

Overview

CASE STUDY – Reverse engineering a website into an ERD. To get some practice with taking a system that uses data of some sort and try to figure out how it was designed as a relational database.

Case Study

In this case study, you will analyze the Hymns section of the Church's website and attempt to reverse engineer its database structure.

View the Hymns section of the Church's website. Hymns

What entities might be used in the database that is supplying the information for this web page?

• The hymns themselves
• Composers of the hymns
• Topics associated with the hymns

What are some of the attributes that would fit within each entity?

(Remember the attributes are not the same as values. An attribute would be ‘title’. Every hymn has the attribute of title. And then later when we insert values into a table; one row’s value or title would be something like ‘The Morning Breaks’. We are only interested in attributes right now, not the values.)

• hymns
  • title
  • first_line
  • number
• composers
  • last_name
  • first_name
• topics
  • topic_name

What data type might be used for each attribute?

• hymns
  • title VARCHAR(45)
  • first_line VARCHAR(45)
  • number INT
• composers
  • last_name VARCHAR(30)
  • first_name VARCHAR(30)
• topics
  • topic_name VARCHAR(20)

How could these entities be related to each other?

Let’s look at the relationship between the hymns and the composers. Each hymn might have one or more composers and each composer may have composed one or more hymns. That’s a many-to-many relationship. For example, the hymn “An Angel from on High” was written and composed by Parley P. Pratt and John E. Tullidge. Parley P. Pratt also wrote a number of other songs as well.

Let’s look at the relationship between the hymn and the topic. Each hymn could have one or more topics associated with it and each topic could belong to one or more hymns. For example the hymn “An Angel from on High” is associated with the topics ‘Book of Mormon’, “Gathering of Israel’, and ‘Restoration of the Gospel’. But these topics could also be used for other songs as well. For example, “Israel, Israel, God it Calling” is also listed under the topic of “Gathering of Israel”. So that one topic can have many hymns associated with it.

Here is an ERD that would fit with this data. Notice I have added a primary key for each of the 3 main entities. Hymn number seemed like a good primary key for the hymns.

                                                           ┌──────────────────────┐
                                  ┌─────────────────┐      │ composer             │
                                  │ hymn_composer   │      ├──────────────────────┤
                                  ├─────────────────┤>────1│ PK comp_id INT       │
┌──────────────────────────┐      │ PK comp_id INT  │      │    lname VARCHAR(45) │
│ hymn                     │1────<│ PK hymn_num INT │      │    fname VARCHAR(45) │
├──────────────────────────┤      └─────────────────┘      └──────────────────────┘
│ PK hymn_num INT          │                                                       
│    title VARCHAR(45)     │    ┌───────────────────┐     ┌─────────────────────┐  
│    first_line VARCHAR(45)│1──<│ hymn_topic        │     │ topic               │  
└──────────────────────────┘    ├───────────────────┤     ├─────────────────────┤  
                                │ PK topic_id INT   │>───1│ PK topic_id INT     │  
                                │ PK hymn_num INT   │     │    topic VARCHAR(45)│  
                                └───────────────────┘     └─────────────────────┘  

The many-to-many relationships were resolved down to 2 one-to-many relationships automatically in Workbench as the ERD was created. The two linking tables contain a composite key with a primary key from each table.

You will do something similar for your homework this weekend; visiting any website and creating an ERD from the data you see there. It doesn’t have to be perfect. I know for a fact that the real database used for the churchofjesuschrist.org hymn site was probably much more complex and had many more entities and attributes. But as long as you give it your best shot of how you think it is design, you will be good.

Again, don’t confuse what you place in the ERD with values that will come later when inserting data into the database. At this point we are just designing, not placing values. Later the hymntable might look something like the image below, but this is later, not this week. For the ERD you create this week we are focusing on the column headers below (the attributes), not the actual values. Your ERD will look more like the previous entity relationship diagram image.

Useful Links:

• Go to: Week Index • Course Home • Canvas