Today I am creating a series of problem and solution. It can be on various subjects. At this moment I am starting with database design. From internet I found some database related problem in which you have to solve the domain design with relational database concept. In my blog I want to share some of these problems with my solution. Please feel free to download the code and review the solution.

Problem

Here I am giving the problem of a personal DVD collection domain. The requirements are given and a database table structure image are also there to help you understand the solution. Following the problem statements and the entities and their relations. In real world as a domain expert you have to design solution from problem statement which is more complex. But here it is not so complex and you can easily pick up the entities and their relationship. All you have to do create the database and create the tables with relations. You can work with any RDBMS. For example here I am using Microsoft SQL Server.

The Entities required should include:
1. Actors
2. Film Titles
3. Film Genres
4. Actor Roles
5. Producers
6. Film Certificates

The Entities are related as follows:
1. An Actor can be involved in many Films, in different Roles
2. A Film can have many Actors
3. A Film Genre can have many different Films
4. A Film Certificate can have many Films
5. A Film can have many Producers
6. A Producer can produce many Films

When asking questions of the database we may need to know:
1. Who stared in a particular film, in what role?
2. Do you have any PG certified films in your collection?
3. Who were the Producers of a particular film?

Personal Dvd Collection

Solution

I am posting my code in two parts. One is to create the database and another is to create the tables with relations.

USE [master]
GO

CREATE DATABASE PersonalDvdCollection ON PRIMARY
(
    NAME = N'PersonalDvdCollection',
    FILENAME = N'C:\DatabaseProblem\PersonalDvdCollection.mdf',
    SIZE = 3MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 10%
),

FILEGROUP FG1 DEFAULT
(
    NAME = N'PersonalDvdCollectionData1',
    FILENAME = N'C:\DatabaseProblem\PersonalDvdCollectionData1.ndf',
    SIZE = 2MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 10%
),
(
    NAME = N'PersonalDvdCollectionData2',
    FILENAME = N'C:\DatabaseProblem\PersonalDvdCollectionData2.ndf',
    SIZE = 2MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 10%
),
FILEGROUP FG2
(
    NAME = N'PersonalDvdCollectionData3',
    FILENAME = N'C:\DatabaseProblem\PersonalDvdCollectionData3.ndf',
    SIZE = 2MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 10%
),
(
    NAME = N'PersonalDvdCollectionData4',
    FILENAME = N'C:\DatabaseProblem\PersonalDvdCollectionData4.ndf',
    SIZE = 2MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 10%
)

LOG ON
(
    NAME = N'PersonalDvdCollectionLog',
    FILENAME = N'C:\DatabaseProblem\PersonalDvdCollectionLog.ldf',
    SIZE = 2MB,
    MAXSIZE = UNLIMITED,
    FILEGROWTH = 10%
)
GO

USE [PersonalDvdCollection]
GO

CREATE SCHEMA Dvd AUTHORIZATION dbo
GO

-- Create tables without Foreign keys.
-- -----------------------------------

CREATE TABLE Dvd.Films
(
    FilmID INT NOT NULL IDENTITY(1, 1),
    Title VARCHAR(50) NOT NULL,
    Story VARCHAR(50) NOT NULL,
    ReleaseDate DATE NOT NULL,
    Duration INT NOT NULL CONSTRAINT dfFilmDuration DEFAULT (3),
    AditionalInformation VARCHAR(100) NULL,

    GenreID INT NOT NULL,
    CertificateID INT NOT NULL,
	
    CONSTRAINT pkFilms PRIMARY KEY (FilmID)
)
GO

CREATE TABLE Dvd.[Certificates]
(
    CertificateID INT NOT NULL IDENTITY(1, 1),
    Content VARCHAR(50) NOT NULL,

    CONSTRAINT pkCertificates PRIMARY KEY (CertificateID)
)
GO

CREATE TABLE Dvd.Genres
(
    GenreID INT NOT NULL IDENTITY(1, 1),  
    Name VARCHAR(50) NOT NULL,

    CONSTRAINT pkGenres PRIMARY KEY (GenreID)
)
GO

CREATE TABLE Dvd.Producers
(
    ProducerID INT NOT NULL IDENTITY(1, 1),
    Name VARCHAR(50) NOT NULL,
    EmailAddress VARCHAR(50) NULL,
    Website VARCHAR(50) NULL,

    CONSTRAINT pkProducers PRIMARY KEY (ProducerID)
)
GO

CREATE TABLE Dvd.FilmsProducers
(
    FilmID INT NOT NULL,
    ProducerID INT NOT NULL,
)
GO

CREATE TABLE Dvd.Actors
(
    ActorID INT NOT NULL IDENTITY(1, 1),
    FirstName VARCHAR(20) NOT NULL,
    MiddleName VARCHAR(10) NULL,
    LastName VARCHAR(20) NOT NULL,
    Notes VARCHAR(50) NULL,

    CONSTRAINT pkActors PRIMARY KEY (ActorID)
)
GO

CREATE TABLE Dvd.Roles
(
    RoleID INT NOT NULL IDENTITY(1, 1),
    Name VARCHAR(20) NOT NULL,

    CONSTRAINT pkRoles PRIMARY KEY (RoleID)
)
GO

CREATE TABLE Dvd.FilmsActorsRoles
(
    FilmID INT NOT NULL,
    ActorID INT NOT NULL,
    RoleID INT NOT NULL,

    CharacterName VARCHAR(50) NOT NULL,
    CharacterDescription VARCHAR(50) NULL
)
GO

-- Now add Foreign keys to create table relations.
-- -----------------------------------------------

ALTER TABLE Dvd.Films
ADD CONSTRAINT fkFilmsWithGenres FOREIGN KEY (GenreID) REFERENCES Dvd.Genres (GenreID)
GO

ALTER TABLE Dvd.Films
ADD CONSTRAINT fkFilmsWithCertificates FOREIGN KEY (CertificateID) REFERENCES Dvd.[Certificates] (CertificateID)
GO

ALTER TABLE Dvd.FilmsProducers
ADD CONSTRAINT fkFilmsProducersWithFilms FOREIGN KEY (FilmID) REFERENCES Dvd.Films (FilmID)
GO

ALTER TABLE Dvd.FilmsProducers
ADD CONSTRAINT fkFilmsProducersWithProducers FOREIGN KEY (ProducerID) REFERENCES Dvd.Producers (ProducerID)
GO

ALTER TABLE Dvd.FilmsActorsRoles
ADD CONSTRAINT fkFilmsActorsRolesWithFilms FOREIGN KEY (FilmID) REFERENCES Dvd.Films (FilmID)
GO

ALTER TABLE Dvd.FilmsActorsRoles
ADD CONSTRAINT fkFilmsActorsRolesWithActors FOREIGN KEY (ActorID) REFERENCES Dvd.Actors (ActorID)
GO

ALTER TABLE Dvd.FilmsActorsRoles
ADD CONSTRAINT fkFilmsActorsRolesWithRoles FOREIGN KEY (RoleID) REFERENCES Dvd.Roles (RoleID)
GO

You can follow this code on GitHub here.