Assignment 1 PDF

Preview

Summary

ASSIGNMENT 1Name : Osama Ahmed ChohanUmlet Diagram :A primary key is unique identification such as student ID and passport number. In this case, I have found many primary keys like eventID to specify the details of events. For the purpose of unique identification, I have listed AddressID and as ther...

Description

ASSIGNMENT 1 Name : Osama Ahmed Chohan

Umlet Diagram :

A primary key is unique identification such as student ID and passport number. In this case, I have found many primary keys like eventID to specify the details of events. For the purpose of unique identification, I have listed AddressID and as there can’t be two event at individual address. So, there are other primary keys including addressID, roundID, teamID, memberID, eventID and resourceID. Multiple teams can play multiple rounds, many members can play single or multiple events, public and local event would be organised at single address, a team can be consisted of one or many members but a member can create many teams.

Relation Schemas : E-Sports(eventID) PK(eventID)

PublicEvent(eventID,eventVenue,date) PK(eventID)

LocalEvent(eventID, winner, runner-up, date, eventVenue, addressID, memberID, roundID) PK(eventID) FK(addressID)->Address(addressID) FK(memberID)->ClubMember(memberID) FK(roundID)->Round(roundID)

Address(addressID, streetNumber, streetName, suburb, city, postCode) PK(addressID)

Member(memberID, addressID, firstName, lastName, email, mobile)

PK(memberID) ClubTeam(teamID,name) PK(teamID)

FK(addressID)->Address(addressID) ElectronicResource(resourceID, eventID, memberID, resourceName) PK( resourceID, eventID, memberID)

Food(eventID,memberID,name,price,rating) PK(eventID, memberID)

Round(roundID, winnerName, winningTeam, teamScore) PK(roundID) FK(teamID)->Team(teamID)

SQL SCRIPT : CREATE TABLE Food ( name VARCHAR(255) , rating INTEGER , price INTEGER ); GO

CREATE TABLE Electronic resources ( resourceID(PK) INTEGER NOT NULL , name VARCHAR(255)

,

PRIMARY KEY(resourceID(PK))); GO

CREATE TABLE PublicEvent ( date DATE , eventVenue VARCHAR(255) ); GO

CREATE TABLE round ( roundID(PK) INTEGER NOT NULL IDENTITY , winningTeam VARCHAR(255) , winnerName VARCHAR(255) PRIMARY KEY(roundID(PK)), FOREIGN KEY() REFERENCES Food()); GO

,

CREATE TABLE esportEvent ( eventId(PK) INTEGER , PRIMARY KEY(eventId(PK)), FOREIGN KEY() REFERENCES PublicEvent()); GO

CREATE TABLE Address ( addressID(Pk) INTEGER , streetNumber INTEGER , streetName VARCHAR(255) , suburb VARCHAR(255) , city VARCHAR(255) , postCode INTEGER

,

PRIMARY KEY(addressID(Pk)), FOREIGN KEY() REFERENCES PublicEvent()); GO

CREATE TABLE ClubTeam ( teamID INTEGER , round_roundID(PK) INTEGER ,

name VARCHAR(255)

,

PRIMARY KEY(teamId) , FOREIGN KEY(round_roundID(PK)) REFERENCES round(roundID(PK)), FOREIGN KEY() REFERENCES Food()); GO

CREATE INDEX ClubTeam_FKIndex1 ON ClubTeam (round_roundID(PK)); GO

CREATE TABLE LocalEvent ( Address_addressID(Pk) INTEGER , esportEvent_eventId(PK) INTEGER , Electronic resources_resourceID(PK) INTEGER NOT NULL , winner VARCHAR(255) , runner-up VARCHAR(255) , date DATE , eventVenue VARCHAR(255)

,

PRIMARY KEY(Address_addressID(Pk), esportEvent_eventId(PK), Electronic resources_resourceID(PK)) , FOREIGN KEY(Address_addressID(Pk)) REFERENCES Address(addressID(Pk)), FOREIGN KEY(esportEvent_eventId(PK))

REFERENCES esportEvent(eventId(PK)), FOREIGN KEY(Electronic resources_resourceID(PK)) REFERENCES Electronic resources(resourceID(PK))); GO

CREATE TABLE ClubMember ( memberID INTEGER NOT NULL , Address_addressID(Pk) INTEGER NOT NULL , givenName VARCHAR(255) , familyName VARCHAR(255) , email VARCHAR(255) , mobile INTEGER

,

PRIMARY KEY(memberID, Address_addressID(Pk)) , FOREIGN KEY(Address_addressID(Pk)) REFERENCES Address(addressID(Pk))); GO

CREATE INDEX ClubMember_FKIndex1 ON ClubMember (Address_addressID(Pk)); GO...