Itech 1006 Database Management System-Database Assessment Answers
Provide a discussion of normalisation including the normal form that each entity is in, a list of relationships with all table names, primary and foreign keys, and attributes indicating their type and purpose.
- Design a relational database for a provided scenario utilising tools and techniques including ER diagrams, relation models and normalisation
- Describe relational algebra and its relationship to Structured Query Language (SQL)
- Design and implement a relational database using a database management system.
Answer:
- A course type can have only one type of pre requisite.
- The bank detail for the Monthly payment will be saved whether it is regular or automatic.
- There may be many courses of a course type.
- A member may join many courses and take any number classes.
- Each course type will have a name.
- Each course of specific course type will also have a name.
Normalization
COURSETYPE (CourseType, CourseNane, NumberOfClassess, Description, MaximumStudentNumbers)
COURSEPREREQUISITE (CourseType, PreRequisite)
STAFF (StaffID, Name, Phone, Address, Email, TaxNumber, JobTitle, WeekRate)
COURSE (CourseID, CourseName, CourseType, SupervisorStaffID, StartDate, EndDate, Notes)
CLASS (ClassID, StaffID, ClassDateTime, CourseID, RoomNumber, Notes)
AWARDS (AwardID, Name, Description)
MEMBER (MemberID, MemberName, Address, Email, Phone, DOB, SpecialReuirements, EmergencyContactName, EmergencyContactNumber, JoiningDate, LeavingDate)
MEMBERCOURSES (MemberID, CourseID)
MEMBERCLASSES (MemberID, ClassID)
MEMBERAWARD (MemberID, AwardID, CourseID, AwardDate, SpecialNotes)
PAYMENT (PaymentMode, PaymentDate, PaidAmount, MemberID)
AUTOMATICMONTHLY (PaymentMode, SuccessFailStatus, BankName, AccountType, AccountNumber, AccountName)
SINGLEDAY (PaymentMode, PaymentMethod)
MULTIPLEPASS (PaymentMode, PaymentMethod, NumberOfPass)
All relations above are in 3 NF.
Conversion of E-R diagram to relational schema
|
Field |
Type |
|
CourseType |
VARCHAR(50) |
|
CourseNane |
VARCHAR(50) |
|
NumberOfClassess |
INTEGER |
|
Description |
VARCHAR(500) |
|
MaximumStudentNumbers |
INTEGER |
|
CourseType |
VARCHAR(50) |
|
PreRequisite |
VARCHAR(50) |
|
StaffID |
INTEGER |
|
Name |
VARCHAR(50) |
|
Phone |
VARCHAR(20) |
|
Address |
VARCHAR(100) |
|
|
VARCHAR(50) |
|
TaxNumber |
VARCHAR(20) |
|
JobTitle |
VARCHAR(50) |
|
WeekRate |
DECIMAL(5,2) |
|
CourseID |
INTEGER |
|
CourseName |
VARCHAR(50) |
|
CourseType |
VARCHAR(50) |
|
SupervisorStaffID |
INTEGER |
|
StartDate |
DATETIME |
|
EndDate |
DATETIME |
|
Notes |
VARCHAR(500) |
|
ClassID |
INTEGER |
|
StaffID |
INTEGER |
|
ClassDateTime |
DATETIME |
|
CourseID |
INTEGER |
|
RoomNumber |
VARCHAR(10) |
|
Notes |
VARCHAR(500) |
|
AwardID |
INTEGER |
|
Name |
VARCHAR(50) |
|
Description |
VARCHAR(500) |
|
MemberID |
INTEGER |
|
MemberName |
VARCHAR(50) |
|
Address |
VARCHAR(50) |
|
|
VARCHAR(50) |
|
Phone |
VARCHAR(20) |
|
DOB |
DATETIME |
|
SpecialRequirements |
VARCHAR(200) |
|
EmergencyContactName |
VARCHAR(50) |
|
EmergencyContactNumber |
VARCHAR(20) |
|
JoiningDate |
DATETIME |
|
LeavingDate |
DATETIME |
|
MemberID |
INTEGER |
|
CourseID |
INTEGER |
|
MemberID |
INTEGER |
|
ClassID |
INTEGER |
|
MemberID |
INTEGER |
|
AwardID |
INTEGER |
|
CourseID |
INTEGER |
|
AwardDate |
DATETIME |
|
SpecialNotes |
VARCHAR(200) |
|
PaymentMode |
VARCHAR(20) |
|
PaymentDate |
DATETIME |
|
PaidAmount |
DECIMAL(5,2) |
|
MemberID |
INTEGER |
|
PaymentMode |
VARCHAR(20) |
|
SuccessFailStatus |
Bit |
|
BankName |
VARCHAR(50) |
|
AccountType |
VARCHAR(50) |
|
AccountNumber |
VARCHAR(20) |
|
AccountName |
VARCHAR(50) |
|
PaymentMode |
VARCHAR(20) |
|
PaymentMethod |
VARCHAR(50) |
|
PaymentMode |
VARCHAR(20) |
|
PaymentMethod |
VARCHAR(50) |
|
NumberOfPass |
INTEGER |
References
Brombarg P. (n.d.), SQL SERVER Database Normalization Basics for Developers, Retrieved from
https://www.nullskull.com/a/1629/sql-server-database-normalization-basics-for-developers.aspx
Cinergix Pty Ltd. (2011), Ultimate Guide to ER Diagrams, Retrieved from https://creately.com/blog/diagrams/er-diagrams-tutorial/.
Buy Itech 1006 Database Management System-Database Assessment Answers Online
Talk to our expert to get the help with Itech 1006 Database Management System-Database Assessment Answers to complete your assessment on time and boost your grades now
The main aim/motive of the management assignment help services is to get connect with a greater number of students, and effectively help, and support them in getting completing their assignments the students also get find this a wonderful opportunity where they could effectively learn more about their topics, as the experts also have the best team members with them in which all the members effectively support each other to get complete their diploma assignments. They complete the assessments of the students in an appropriate manner and deliver them back to the students before the due date of the assignment so that the students could timely submit this, and can score higher marks. The experts of the assignment help services at urgenthomework.com are so much skilled, capable, talented, and experienced in their field of programming homework help writing assignments, so, for this, they can effectively write the best economics assignment help services.
