代做INFOSYS 222 Database Systems (Exam) SEMESTER TWO, 2022代写留学生SQL 程序

2024.10.18 - 首页 >> Algorithm 算法

INFOSYS 222 Database Systems (Exam)

SEMESTER TWO, 2022

Data Modelling - DRA

The Data Research Alliance (DRA) wishes to manage its membership using a database. They want their database to capture the following data and enforce the following business rules.

Member

The information to be stored about a member includes a member ID, first name, last name, address, email and mobile. Membership is usually recommended by another member. It is necessary to keep a record of which existing member recommends which new member.

Membership

There are four types of membership: student, associate, fellow and elite. The information to be stored about member type includes member type number and member type name. Over time the member type of a member could change. As such, it is necessary to store the details about when a member joins the DRA, which member type they start as; when they change their member type; and when they give up their membership and leave the DRA. The detail as to how a member upgrades the member type, say, from an associate to a fellow, or from a fellow to an elite, is outside the scope of this database.

In order to retain an upgraded member type with DRA, a member must accumulate a certain number of professional training points during a calendar year. These points are awarded for attending events organised by the DRA. The minimum number of points that a member must accumulate depends upon the member type. For example, student members and associate members do not have to accumulate any points; a fellow member must accumulate at least ten points; and an elite member must accumulate twenty points. The number of points to be accumulated per member type may change over time, but they will only change at the beginning of a calendar year. If insufficient points are accumulated at the one-year mark of a membership, the member will be downgraded to a lower membership type.

Event

The details to be stored about an event includes event ID, event name, event description, date of the event, start time and duration, and the number of points per member type awarded for attendance. One event may incorporate other events. For example, the Data Enthusiasts Conference which often lasts for four days will be recorded as one event, but it is made up of a number of smaller events.

Draw a logical ERD in crow’s foot notation for the case described above with stated design decisions and assumptions.

Please note: all important design decisions and / or assumptions made (3-5) must be clearly listed. No marks will be given if there is no design decision or assumption. All entity sets, attributes including primary and foreign keys, and relationships including cardinalities must be shown as appropriate. Following database design principles, the ERD should not contain redundant entity sets, relationships or attributes.

Normalisation - Purchase Order

Derive a set of relations in third normal form. (3NF) using the given document above. Show each step of normalisation clearly. Indicate primary keys and foreign keys with labels or solid/dashed lines. You must state your design decisions and / or assumptions made (1-2) for the normalisation.

No marks will be given if there is no design decision or assumption.

Lotto Database Schema

CREATE TABLE Combination

(combinationNo INTEGER PRIMARY KEY NOT NULL,

N1 INTEGER NOT NULL,

N2 INTEGER NOT NULL,

N3 INTEGER NOT NULL,

N4 INTEGER NOT NULL,

N5 INTEGER NOT NULL,

N6 INTEGER NOT NULL,

UNIQUE (N1, N2, N3, N4, N5, N6)

);

CREATE TABLE Draw

(drawNo INTEGER PRIMARY KEY NOT NULL,

drawDate DATE NOT NULL,

drawName TEXT,

winCombinationNo INTEGER,

FOREIGN KEY (winCombinationNo) REFERENCES Combination (combinationNo)

);

CREATE TABLE Ticket

(ticketNo INTEGER PRIMARY KEY NOT NULL,

drawNo INTEGER NOT NULL,

soldAt TEXT NOT NULL,

FOREIGN KEY (drawNo) REFERENCES Draw (drawNo)

);

CREATE TABLE Line

(ticketNo INTEGER NOT NULL,

lineID TEXT NOT NULL,

combinationNo INTEGER NOT NULL,

PRIMARY KEY (ticketNo, lineID),

FOREIGN KEY (ticketNo) REFERENCES Ticket (ticketNo),

FOREIGN KEY (combinationNo) REFERENCES Combination (combinationNo)

);

(a) SQL - Special Draw