Let’s kick off the Mini Project by examining the database schema. The project involves storing and manipulating data related to movies (in my case, DVDs I own).
For this project I am going to concentrate on three key relationships:
1. Movies have at least one director
2. Movies have one or more cast (actors/actresses)
3. Movies belong to one or more genres (e.g. Horror, Drama)
4. A Director may direct more than one film
5. Actors/Actresses may appear in more than one film
As such, I have modelled the database schema which you may observe from the diagram below:
You may download a T-SQL script to create this schema from this location here. Requires Microsoft SQL Server 2005 or Microsoft SQL Server 2008.
I’ve also published a PDF version (originally from Microsoft Visio) of the schema here.
Some notes about the schema:
– This is a basic schema, and the lack of complexity is intentional
– This schema is meant to hold only a couple of thousand records (perhaps around 2,000 rows in the Movie table) at best, so it has not been tuned for high volume or ‘chatty’ small queries.
– Join tables contain composite primary keys for reasons explained in later posts
Lastly, the schema has some specific reasons for the column types which shall become clearer soon.
Stay tuned for Part 2, coming shortly.
