project 2 data structure design. objectives to create two data structure designs to understand the...
TRANSCRIPT
Project 2Data Structure Design
Objectives
• To create two data structure designs• To understand the advantages of linking
tables• To be able to describe the advantages and
disadvantages of your structures
Marks
Produce designs for the data structure (3 marks) Mark
Produce a file structure for your database. 1
Produce alternative designs. 2
Write down a list of advantages and disadvantages of each structure and give reasons why your choice is best.
3
Introduction
• You will need to create two database structures for your project and each will contain the field names, data types, properties and validation rules.
• One of the designs will be the same as the two tables in your Project 2 database and the other one will be different.
• The differences can be:– One design has one table while the other has two– There could be some fields in one design that are missing in the
other– The data types of a particular field are different– The validation rules are different
• You then need to say what is good and bad about each design and why you chose the one you did. The changes must be appropriate.
Design 1
TABLE - MOTORBIKESFIELD NAME DATA TYPE PROPERTIES VALIDATION RULE
Motorbike ID Autonumber Long Integer
Make Text 20 Characters Suzuki OR Honda OR Kawasaki
Model Text 20 Characters
Colour Text 20 Characters N/A
Price Number Currency >0
Year Number Integer N/A
Date Bought Date Short date N/A
TABLE - CUSTOMERSFIELD NAME DATA TYPE PROPERTIES VALIDATION RULE
Customer ID Autonumber Long Integer
Surname Text 20 Characters
Forename Number Currency
Address Number Integer
Motorbike ID Number Long integer
Design 2
FIELD NAME DATA TYPE PROPERTIES
Make & Model Text 40 Characters
Colour Text 20 Characters
Price Number Currency
Year Number Integer
Date Bought Date Short date
Name Text 60 Characters
Address Text 120 Characters
Flat File vs Relational Database
Splitting Name & Address
Splitting Name & Address
FIELD NAME DATA TYPE PROPERTIES
Title Text 4 Characters
Forename Text 20 Characters
Surname Text 20 Characters
Street Text 30 Characters
Town Text 40 Characters
Post Code Text 8 Characters
FIELD NAME DATA TYPE PROPERTIES
Name Text 40 Characters
Address Text 80 Characters
DESIGN 1
DESIGN 2
Yes/No vs Text Fields
Yes/No vs Text Fields
FIELD NAME DATA TYPE PROPERTIES
Taxed Yes/No Yes/No
Suitable for Learners Yes/No Yes/No
MOT Yes/No Yes/No
FIELD NAME DATA TYPE
PROPERTIES VALIDATION RULE
Taxed Text 40 Characters Yes OR No
Suitable for Learners
Text 80 Characters Yes OR No
MOT Text 80 Characters Yes OR No
DESIGN 1
DESIGN 2
Changing Data Type II
FIELD NAME DATA TYPE PROPERTIES
Taxed Yes/No Yes/No
MOT Yes/No Yes/No
DESIGN 1
FIELD NAME DATA TYPE
PROPERTIES VALIDATION RULE
Taxed (months)
Number Byte >=0 AND <=12
MOT (months)
Number Byte >=0 AND <=12
DESIGN 2
Number Lengths
Byte 0 - 255 1 byte of memory
Integer –32,768 to 32,767
2 bytes of memory
Long Integer –2,147,483,648 to 2,147,483,647
4 bytes of memory
YOU MUST LEAVE CURRENCY AND AUTONUMBER FIELDS AS LONG INTEGER
Number Lengths
FIELD NAME DATA TYPE PROPERTIES
Mileage Number Long Integer
Year Made Number Long Integer
Number of Owners Number Long Integer
FIELD NAME
DATA TYPE
PROPERTIES VALIDATION RULE
Mileage Number Long Integer >=0
Year Made Number Integer >=1990 AND <=2007
Number of Owners
Number Byte >=1 AND <=10
Date Format
Short Date 19/12/06
Medium Date 19-Dec-06
Long Date 19 December 2006
Short Time 17:34
Medium Time 05:34 PM
Long Time 17:34:43
Date Format
FIELD NAME DATA TYPE PROPERTIES
Date Bought Date/Time Long Date
Date Sold Date/Time Long Date
FIELD NAME
DATA TYPE
PROPERTIES VALIDATION RULE
Date Bought Date/Time Short Date >=01/01/05
Date Sold Date/Time Short Date >=01/01/07
Homework
Advantages & Disadvantages
• For both designs write a list of advantages write a list of disadvantages
• For your chosen design explain why you chose it by summarising the advantages and disadvantages of the designs