Video Rental Store SQL
Introduction to SQL

Previously, you did a draft Entity Relationship design for a video rental store.
(Instructions are below, for your reference.)

Using SQL, create entity and relationship tables to match your design.
Include at least one sample row (‘tuple’) for each table. You need to have enough information to be able to demonstrate your queries.

Create queries, and verify that they work correctly.

Video Rental Store ER
Introduction to Database

In the old days, before online streaming services, people used to actually rent physical copies of DVDs and VCRs.  Movies would be organized by category (horror, comedy, discount, etc.).  You could also do a search for a title, actor, director, genre, studio, etc.

Recall that an ENTITY is something about which you are collecting information.
(If you think the store itself is an entity, rethink that. )
(I) What are the ENTITIES that customers want to have information about?
[More than 1, of course]

An ATTRIBUTE is the information you have about the attribute.
(II) What are the ATTRIBUTES of each entity?

A KEY is the unique identifier for each individual entity.  (You will later learn about minimal key, foreign key, alternative key, etc.)  A KEY is usually a short identifier that does not change.  Often a key is simply an ID number.  A database can generate KEYs for newly entered data.  

A RELATIONSHIP is a connection between two (or more) ENTITY types.
For example, in a family, RELATIONSHIPS include parent, child, sibling.  
(III) What RELATIONSHIPS exist between the entities you have?

An Entity Relationship Diagram is a visual diagram of the relationships between your entities.  Normally, an entity is a rectangle, a relationship is a diamond, and lines are drawn between the relationship and the entities it involves.
[Later we will learn about Relational Entities, which are a diamond embedded within a square.]

A QUERY is a request for information.  The simplest query is often, give me the attributes of the entity with this KEY.  Or, you could request all the entities that have a relationship with the specified entity.  (All advisees of a professor, for example.)

What QUERIES would a customer be likely to have, as they are looking for a movie to rent?
(Answering the queries can get more complex.)

Quick checks for quality of your database design:
Data that might change should only be stored ONCE in the database.
(KEYS can appear in more than one table, because they are used as identifiers. However, a KEY does not change.)

Each KEY identifies exactly one ENTITY.
All the information in an entity table is determined by the key, the whole key, and nothing but the key. (We will learn about normalization later).

Only one piece of information is in each entry.  For example, you cannot put more than one professor’s name in the ADVISE relationship.  (If a student has multiple advisors, you would need multiple lines in the ADVISE table to indicate that.)

Now do your draft writeup.
For each ENTITY, list the key and other attributes the ENTITY has.
These are the headings for the ENTITY table.

For each RELATIONSHIP, list the key for each ENTITY in the relationship.
For a simple, binary relationship, the ONLY thing in the relationship table is the two KEYS. (Later we will learn that some relationships are 1:many, and some are many : many.)

ONLY information about ONE entity should be in an entity table.
Do not even think about putting information about the Professor in the Student table.

LIST the questions a customer might have.  We will make them into QUERIES later.