Car Tax Renewal Database

Car Tax Renewal Database Database Concepts David O' Connor t00130990


Page 3 " 6






CP: Database Concepts


Assignment 01 – Data Modelling

Submitted By: David O' Connor (KCOMP_G_Y6)
Submitted on: 30th November 2014




Table of Contents
1. Data Model 3
2. Relational Schema 4
3. Database Schema 5



Data Model
Payment
Payment_Id {pk}
Pay_Date
Amount
Method
Type




1 …*1 …*
1 …*
1 …*
Is forIs for
Is for
Is for
Vehicle
RegNo {pk}
Make
Model
Colour
NRD
11PIN
1
1
Belongs toBelongs to0 …*0 …*Is reg. withIs reg. with11
Belongs to
Belongs to
0 …*
0 …*
Is reg. with
Is reg. with
1
1
Owner
Owner_ID {pk}
Forename
Surname
Street
Town
County
Phone_Number
Email
Licence Authority
LA_Code {pk}
Description
11
1
1
11Refers toRefers to
1
1
Refers to
Refers to
Assessment
Ass_Code {pk}
Description
R3
R6
R12
Refers toRefers to
Refers to
Refers to

RegOwner
RegOwnerDate {pk}
Owner_ID {pk}
RegNo {pk}
Forename
Surname
Street
Town
County
1 …*1 …*1 …*1 …*
1 …*
1 …*
1 …*
1 …*










Relational Schema

Owner (Owner_ID, Forename, Surname, Street, Town, County, Phone_Number, Email)

Vehicle (RegNo, Make, Model, Colour, NRD, PIN)

Payment (Payment_Id, Pay_Date, Amount, Method, Type)

Licence Authority (LA_Code, Description)

RegOwner (RegOwnerDate, Owner_ID, RegNo, Forename, Surname, Street, Town, County)

Assessment (Ass_Code, Description, R3, R6, R12)




Database Schema

Schema: Car Taxation Renewal

Relation: Owner
Attributes:
Owner_ID int (6) NOT NULL,
Forename char (10) NOT NULL,
Surname char (30) NOT NULL,
Street char (20),
Town char (20),
County char (10),
Phone_Number int (15)
Email char (30)
PRIMARY KEY: Owner_ID


Relation: Vehicle
Attributes:
RegNo char (15) NOT NULL,
Make char (20) NOT NULL,
Model char (20) NOT NULL,
Colour char (10) NOT NULL,
NRD char (10) NOT NULL,
PIN int (5)
PRIMARY KEY: RegNo
FOREIGN KEY: RegNo references RegOwner

Relation: Payment
Attributes:
Payment_Id int (6) NOT NULL,
Pay_Date char (10) NOT NULL,
Amount int (6) NOT NULL,
Method char (20) NOT NULL,
Type char (10)
PRIMARY KEY: Payment_Id
FOREIGN KEY: RegNo references Vehicle






Relation: Licence Authority
Attributes:
LA_Code int (5) NOT NULL,
Description char (15),
PRIMARY KEY: LA_Code
FOREIGN KEY: RegNo references Vehicle

Relation: RegOwner
Attributes:
RegOwnerDate char (10) NOT NULL,
Owner_ID int (10) NOT NULL,
RegNo char (15) NOT NULL,
Forename char (10),
Surname char (30),
Street char (20),
Town char (20),
County char (10),
PRIMARY KEY: RegOwnerDate, Owner_ID, RegNo
FOREIGN KEY: Owner_ID references Owner

Relation: Assessment
Attributes:
Ass_Code int (6),
Description char (20),
R3 int (6),
R6 int (6),
R12 int (6)
PRIMARY KEY: Ass_Code
FOREIGN KEY: RegNo references Vehicle