1

I have an assignment due next week, and for that assignment I am supposed to learn MySQL and create an entity-relationship diagram (ERD).

My current attempt

Now I've made this ERD

enter image description here

The issue

I went to my professor for feedback and he just told me that it is not a good ERD and refused to give me any further feedback.

I'm still a noob, but my question to you is: How do I improve this ERD? I was thinking about removing the Ticket entity type since that might be confusing...

3
  • Even if this was on-topic nobody would be able to answer it without knowing what this is all about. When you post it on whatever site is appropriate for this question, make sure to include a detailed description of your assignment. Currently it sounds like you should create some reandom ERD. Commented Jan 21, 2018 at 17:13
  • Thing is, I am supposed to create a random ERD. Only requirements are that I use at least 5 tables. Thats why it isnt descriptive. Its supposed to be an erd for a cinema and its supposed to read where you can see which films and when etc.
    – Basel Noeman
    Commented Jan 21, 2018 at 17:35
  • 1
    An ERD is a model of a real-life situation. Decide what questions have to be answered in this situation. Capture the information needed to answer those questions. Arrange it well according to the rules of normalisation. Name the objects well, with strong names that are commonly used by people engaged in this situation. This includes the relationships, so avoid "has", "is_a" etc. Commented Jan 22, 2018 at 11:29

1 Answer 1

2

The following are examples used to convey the point, there are multiple instances of each of these examples within your ERD.

Naming

Consistency is key. Some table names are pluralised (Seats) some are not (Ticket). Your table contains data about many tickets so I normally prefer pluralised table names.

You're prefixing every column with the name of the table. This makes for column names like staff_staff_id, which isn't instantly understandable to readers. If I named the primary key of Staff ID then you store staff.id and you might access tickets.staff_id, which makes everything more understandable.

Relations

There's a couple of relations that don't exist, for example you've got seat_id in Ticket (makes sense if you have preassigned seating) but no relation to Seats.seats_id? Why not? If your cinema/theatre doesn't have preassigned seating then you don't need this data.

There are some duplicates within tables. Why does Ticket contain both staff_id and staff_staff_id?

Data duplication

There are some unnecessary (unenforced) relations that are allowing for data inconsistencies. If a MovieTime must happen within a Hall an a ticket must be at a MovieTime then there's no need for Ticket to contain any information about the hall in which the movie is going to be shown.

It's unclear what the difference between the time_of_screening and screening_time is. Maybe one of them is the number of minutes the screening runs for? This information is already within Movie and so is another duplication.

Data

There's no need to persist the age as you've already got the date of birth. I've written about this on Stack Overflow a few times.

It's quite an assumption that all staff and movie names will be 45 characters or less.

I find the inclusion of sex in the staff table a little off-putting. What's the purpose of storing this information? Are the chromosomes of the person selling you a movie ticket that important in this cinema/theatre. I also don't understand why it's 10 characters long.

How to start

Think about exactly what information is required to go within each table. If you're not sure it's required then don't add it in. If you need to add something later then so be it, but you haven't included a lot of unnecessary data. I'd set-up some test tables with dummy data to see if what you're creating will actually work.

Every relation to another table should be enforced. If you've got some unenforced relations then either add a foreign key constraint or remove the column.

Your cinema probably has tickets, so removing the data will prevent you selling tickets.

1
  • +1 for "Consistency is key". Personally, I use singular names for tables (a table is a set) - but, it's not as important as having a standard and sticking to it!
    – Vérace
    Commented Jan 22, 2018 at 0:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.