New! Hire Essay Assignment Writer Online and Get Flat 20% Discount!!Order Now

ITECH 1006 Database Management System

Published : 07-Oct,2021  |  Views : 10

Question:

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:

  1. A course type can have only one type of pre requisite.
  2. The bank detail for the Monthly payment will be saved whether it is regular or automatic.
  3. There may be many courses of a course type.
  4. A member may join many courses and take any number classes.
  5. Each course type will have a name.
  6. 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)

Email

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)

Email

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 

http://www.nullskull.com/a/1629/sql-server-database-normalization-basics-for-developers.aspx 

Cinergix Pty Ltd. (2011), Ultimate Guide to ER Diagrams, Retrieved from http://creately.com/blog/diagrams/er-diagrams-tutorial/.

Get An Awesome Price Quote For Your Paper – Absolutely FREE!
    Add File
    Files Missing!

    Please upload all relevant files for quick & complete assistance.

    Our Amazing Features

    delivery

    No missing deadline risk

    No matter how close the deadline is, you will find quick solutions for your urgent assignments.

    work

    100% Plagiarism-free content

    All assessments are written by experts based on research and credible sources. It also quality-approved by editors and proofreaders.

    time

    500+ subject matter experts

    Our team consists of writers and PhD scholars with profound knowledge in their subject of study and deliver A+ quality solution.

    subject

    Covers all subjects

    We offer academic help services for a wide array of subjects.

    price

    Pocket-friendly rate

    We care about our students and guarantee the best price in the market to help them avail top academic services that fit any budget.

    Getting started with MyEssayAssignmentHelp is FREE

    15,000+ happy customers and counting!

    Rated 4.7/5 based on
    1491 reviews
    ;