Database Implementation
Assignment
Task 1:
Create Student Table
Create BookCopy Table
Create Loan Table
FORM A: Add Student
FORM A: Save Trigger
FORM A: Generate Course List Trigger
FORM B: Student’s Loan Details
FORM C: Menu
TASK 2:
Part A
Figure: Entity Relationship Diagram
1st Normalization Form
Customer Table
Number Name Address
C14 G. Brown 2 Liverpool Road
C16 L. Gray 7 Corporation Street
C20 G. Black 6 Walnut Tree
C23 J. White 5 Moorside Close
C24 D. White 5 Moorside Close
Account Table:
Number DateOfOpen typeCode type
12346768 12/04/17 A1 saving
67896377 20/02/18 A3 credit card
23574879 25/03/19 A1 saving
34565767 25/03/19 A2 current
86573526 30/05/19 A3 credit card
15657868 10/06/19 A2 current
45765768 10/06/19 A1 saving
58878779 15/11/19 A2 current
Payment Instruction:
Number dateOfInstruction typeCode type creditor description
2356 01/05/19 1 Direct debit BT quarterl
2447 04/02/20 1 Direct debit British Gas monthly
3000 01/05/19 2 Standing orderBT £30 monthly
3000 01/05/19 1 Direct debit TV Licence annually
6555 10/06/19 2 Standing Car Ware £20 weekly
6555 10/06/19 1 Direct debit North Water quarterly
7452 10/06/19 1 Direct debit North Water monthly
7684 10/02/20 1 Direct debit HSBC monthly
2nd Normalization Form:
Customer Table:
Primary Key: Number
Number Name Address
C14 G. Brown 2 Liverpool Road
C16 L. Gray 7 Corporation Street
C20 G. Black 6 Walnut Tree
C23 J. White 5 Moorside Close
C24 D. White 5 Moorside Close
Account Table:
Primary Key: Number
Number DateOfOpen typeCode type
12346768 12/04/17 A1 saving
67896377 20/02/18 A3 credit card
23574879 25/03/19 A1 saving
34565767 25/03/19 A2 current
86573526 30/05/19 A3 credit card
15657868 10/06/19 A2 current
45765768 10/06/19 A1 saving
58878779 15/11/19 A2 current
Payment Instruction:
Primary Key: Number + type
Number dateOfInstruction typeID creditor description
2356 01/05/19 1 BT quarterly
2447 04/02/20 1 British Gas monthly
3000 01/05/19 2 BT £30 monthly
3000 01/05/19 1 TV Licence annually
6555 10/06/19 2 Car Warehouse £20 weekly
6555 10/06/19 1 North Water quarterly
7452 10/06/19 1 North Water monthly
7684 10/02/20 1 HSBC Mortgage monthly
PaymentType Table: (New)
typeID typeCode type
1 1 Direct debit
2 2 Standing order
3rd Normalization Form:
Customer Table:
Primary Key: Number
Number Name Address
C14 G. Brown 2 Liverpool Road
C16 L. Gray 7 Corporation Street
C20 G. Black 6 Walnut Tree
C23 J. White 5 Moorside Close
C24 D. White 5 Moorside Close
Account Table:
Primary Key: Number, Foreign Key: typeCode
Number DateOfOpen typeCode
12346768 12/04/17 A1
67896377 20/02/18 A3
23574879 25/03/19 A1
34565767 25/03/19 A2
86573526 30/05/19 A3
15657868 10/06/19 A2
45765768 10/06/19 A1
58878779 15/11/19 A2
AccountType Table: (NEW)
Primary Key: typeCode
typeCode type
A1 saving
A2 current
A3 credit card
Primary Key: Number + type
Foreign Key: typeID
Number dateOfInstruction typeID creditor description
2356 01/05/19 1 BT quarterly
2447 04/02/20 1 British Gas monthly
3000 01/05/19 2 BT £30 monthly
3000 01/05/19 1 TV Licence annually
6555 10/06/19 2 Car Ware £20 weekly
6555 10/06/19 1 North Water quarterly
7452 10/06/19 1 North Water monthly
7684 10/02/20 1 HSBC monthly
PaymentType Table: (New)
typeID typeCode type
1 1 Direct debit
2 2 Standing order
Part B:
There are three guideline common designs, each with extending levels of standardization: First
Normal Form (1nf): Each field in a table holds particular information. For example, in a laborer
overview, every one table may hold stand apart origination date field. Second Normal Form
(2nf): Each field in a table that isn't a determiner of the substance of a substitute field must itself
be a limit of substitute fields in the table. Third Normal Form (3nf): No twofold information is
permitted. In this manner, for example, if two tables both oblige an origination date field, the
origination date information may be isolated into a different table, and the two unique tables may
then get to the origination date information through a record field in the origination date table.
Any change to an origination date would normally be reflecting in all tables that association with
the origination date table.
Advantages of standardization:-
More modest data set: By forgoing twofold data, you will have the ability to lessen the overall
size of the information base.
Better execution:
1. Narrow tables: Having every one of the more changed tables allows your tables to have less
segments and allows you to fit more records for each data page.
2. Fewer records for each table mean speedier upkeep tasks, for instance, list changes.
3. Only join tables that you require. More stupendous general information base affiliation.
4. Information consistency inside the information
Drawbacks of standardization:-
1. Requires more joins to get the desired impact. A roughly made inquiry can chop the
information base down
2. Maintenance overhead. The higher the degree of standardization, the more staggering the
measure of tables in the information base.
3. More tables to join: By fanning out your data into more tables, you grow the need to join
tables.
4. Data model is irksome to request against: The data model is progressed for arrangements,
not for uncommonly selected addressing.
5. Tables hold codes instead of certifiable data: Repeated data is taken care of as codes rather
than huge data. Thusly, there is reliably a need to go to the inquiry up table.
