ITECH 1006 Database Management Systems-Merchandise Stock Merchandise R
For this first stage, APL are only interested in maintaining data at the club level, to ensure that the clubs are tracking well for future success. Therefore, the APL needs information regarding the running and maintenance of the clubs, including their players and coaches, stadiums, sponsors and sponsor contributions, members, and all saleable merchandise. Any other club financial requirements, including club assets and running inventory are kept on separate financial databases, and are not part of this database project.
All attributes containing metrics will be sourced from club databases and are accumulated values in this database. i.e. player attributes like “number of tackles” etc., will be read from the club database and then added to the current value in this database.
Answer:
Coaches can have their supervisors on optional basis.For a club to have a merchandise it must own its branded merchandise stock. Therefore, merchandise is a weak relation.A club can have one or many sponsors on optional basis where the relation between the club and sponsors is brought in by the amount the sponsor offers and the type of sponsorship that sponsor offers.
Coaches can have only one division and the players can have maximum of 3 divisions.Players exists in form of two categories, goalkeeper players or field players.There exist two types of relation, club to sponsorship relation and sponsorship to sponsor relation. There must exist attributes that makes the club have a relation the sponsor which is the amount and the type of sponsorship.
Club to sponsorship relation is a 1 to M optional relation where the club decides whether to have many sponsors or not. The relation between the sponsors to sponsorship is a 1 mandator
y to M optional relation. If sponsor exists in the sponsors’ entity, that sponsor must have at least one sponsorship.A club to members relation is a 1 to M relation on mandatory basis because a club must have members. One club should have many members for it to be a club.
A club should have merchandise because this is a basic need for that club. The club to merchandise relation is a 1 to M mandatory relation where a club can have one or many merchandise. Merchandise-stock merchandise relation This is a 1 to M relationship where one stock can make many merchandise due to company brandings. As well the relation is weak because merchandise exists because there is stock available.
This is a 1 to M relationship since one set of merchandise can be sold to many people because one brand type may be composing many T-shirts.This is a 1 to 1 relation where every club should have one stadium. Therefore the club owns the stadium and the stadium is owned by certain club.A club can have one or many coaches. But that club should have at least one coach. Therefore the relation between the club and the coaches is a 1 to M mandatory relationship. Where it is a mandatory for a club to have one coach.
This is a 1 to 1 optional relationship because it is not mandatory for a coach to have a supervisor since supervisors don’t have supervisors. One coach should have at most one supervisor.This is a 1 to 1 relation where one coach coaches in one division on mandatory basis. There should exist at most one division record for that coach.A club should have players. It is mandatory for a club to have many players for it to be a club. Therefore the relation between the club and the players is a 1 to M mandatory relationPlayers play in different divisions. But every player should play in at least one division. Therefore the relation between the players and player division is a 1 to M optional relation where it is not mandatory for a player to play in more than one division.
A player should be either a goal keeper or a field player but not both at the same time. The relation between a player and goalkeepers is a 1 to 1 optional relationship where the player might be a goalkeeper. The same case applies to field players where players to field-players relation is 1 to 1 optional relation.In this table, there exist no repeating groups. Therefore our table qualifies to be in 1NF. Our dependencies in the table between the key attribute and the non-key attribute is a functional dependency. Since there exists no partial dependencies and there exist functional dependency, our table qualifies to be in 2NF and in 3NF.
There is no repeating groups in our table hence our table is in 1NF. If we consider the non key attributes the name and the address of the sponsor, we only find that there only exist full dependencies between them and the primary key sponsor ID. Therefore our table is in 2NF and in 3NF.
Our table is in 3NF since there is no repeating groups, no partial dependencies, no transitive dependencies but there only exist functional dependency between the non-key attributes and the key attribute.MEMBER (memberID, clubID, firstName, lastName, address, city, postcode, email)
Our table has repeating group of postcode, city and address. This makes our table fail to be normalized. But we should not normalize the table as only the address details are only used by the club members. If we split our table to create postcode table, we will replace it with a foreign key and the number of data rows remains the same. We assume that would be double work as the data splitting does not reduce our table row size.
Our table is in 1NF since we find no repeating group. Our non-key attribute fully depends on the key attribute. Therefore our table is in 2NF and in 3NF since there exists only functional dependencies.MERCHANDISE (machID, clubID,stockID, quantity In this table, there is no cases of repeating groups and the non-key attributes fully depends on the key attribute. The merchandise ID is the primary key and the other attributes fully depends on it.SALE (salesID, merch ID, price, quantity)
Our table is 1NF since no repeating group is found in our table. There exists no cases of partial or transitive dependencies. Our primary key the sale ID is fully depended by the other non-key attributes. This makes our table to qualify to be in 2NF and as well qualify to be in 3NF as there exist functional dependencies only.STADIUM (stadID, clubID, std_name, capacity, perc_cumulative, executive_suite)
This table is in 1NF as there exists no repeating group. There exists no partial dependency or transitive dependency making our table qualify to be in 2NF. Our non-key attributes fully depends on the key attribute which qualifies our table being in 3NF.
This table is 1NF for there exists no repeating group. The table qualifies to be in 2NF for no cases of partial dependency and finally our table is in 3NF for there is only functional dependencies between the key attribute and the PLAYER(player_ID, club_ID, firstName, lastName, DOB, gamesPlayed, salary, type)
Our table has no repeating groups, no partial or transitive dependencies but all non-key attributes functionally depends on the key attribute. Therefore our table qualifies to be in 3NF.FIELDPLAYER(fdID, playerID, onTarget, assists, passes, tackles, penalties)
This table has no cases of repeating groups making it qualify to be in 1NF. The table has no partial or transitive dependencies making it qualify to be in 2NF. Since there exists functional dependencies only, our table qualifies to be in 3NF.GOALPLAYER(gpID, playerID, freeKicks, GoalKicks, saves, concededGoals)
Our table is in 1NF for there is no repeating group. All the non-key attributes fully depends on the key attribute and therefore our table is in 2NF and in 3NF.PLAYERDIVISON (plDivID, playerID, divID, noOfGames This table is in 1NF since there exist no repeating groups. There is no cases of partial or transitive dependency making our table qualify to be in 2NF. Since our non-key attributes fully depends on the key attribute, there exists functional dependencies and therefore our table qualifies to be in 3NF.
|
ATTRIBUTES |
DATA TYPE |
PROPERTY |
|
ClubID |
Integer |
PK |
|
Name |
Varchar |
|
|
State |
Varchar |
|
|
city |
Varchar |
|
|
|
Varchar |
|
|
SpID |
Integer |
PK |
|
Name |
Varchar |
|
|
Address |
Varchar |
|
|
Spp_ID |
Integer |
PK |
|
ClubID |
Integer |
FK References (CLUB.clubID) |
|
SpID |
Integer |
FK References (SPONSOR.SpID) |
|
Spp_Type |
Varchar |
|
|
amount |
double |
|
|
MemberID |
Integer |
PK |
|
ClubID |
Integer |
FK References (CLUB.ClubID) |
|
firstName |
Varchar |
|
|
lastName |
Varchar |
|
|
Address |
Varchar |
|
|
city |
Varchar |
|
|
postcode |
Varchar |
|
|
|
Varchar |
|
|
stockID |
Integer |
PK |
|
type |
Varchar |
|
|
MerchID |
Integer |
PK |
|
ClubID |
Integer |
FK References (CLUB.ClubID) |
|
StockID |
Integer |
FK References (MERCHSTOCK.StockID) |
|
Quantity |
Varchar |
|
|
SalesID |
Integer |
PK |
|
MerchID |
Integer |
FK References (MERCHANDISE.merchID) |
|
price |
double |
|
|
quantity |
Integer |
|
|
StadID |
Integer |
PK |
|
Club_ID |
Integer |
FK References (CLUB.ClubID) |
|
Std_name |
Varchar |
|
|
capacity |
Integer |
|
|
Perc_cummulative |
double |
|
|
Executive_Suite |
integer |
|
|
DivID |
Integer |
PK |
|
type |
Varchar |
|
|
Description |
Text |
|
|
CoachID |
Integer |
PK |
|
ClubID |
Integer |
FK References (CLUB.clubID) |
|
DivID |
Integer |
FK References (DIVISION.DivID) |
|
SupvID |
Integer |
FK References (COACH.CoachID) |
|
firstName |
Varchar |
|
|
lastName |
Varchar |
|
|
gamesCoached |
Integer |
|
|
PlayerID |
Integer |
PK |
|
ClubID |
Integer |
FK References (CLUB.clubID) |
|
FirstName |
Varchar |
|
|
lastName |
Varchar |
|
|
DOB |
Date |
|
|
gamesPlayed |
Integer |
|
|
salary |
double |
|
|
plDivID |
Integer |
PK |
|
PlayerID |
Integer |
FK References (PLAYER.PlayerID) |
|
DIvID |
Integer |
FK References (DIVISION.DivID) |
|
NoOfGames |
Integer |
|
|
gpID |
Integer |
PK |
|
PlayerID |
Integer |
FK References (PLAYER.PlayerID) |
|
freeKicks |
Integer |
|
|
saves |
Integer |
|
|
ConcededGoals |
Integer |
|
|
goalKicks |
Integer |
|
|
fdID |
Integer |
PK |
|
PlayerID |
Integer |
FK References (PLAYER.PlayerID) |
|
onTarget |
Integer |
|
|
assists |
Integer |
|
|
passes |
Integer |
|
|
tackles |
Integer |
|
|
penalties |
Integer |
|
References
Johnson, E., & Jones, J. (2008). A Developer's Guide to Data Modelling for SQL Server. Addison-Wesley.
Rosenberg, D. (1999). Use case driven object modelling with UML.
Rumbaugh, J. (1999). The unified modelling language reference manual.
Buy ITECH 1006 Database Management Systems-Merchandise Stock Merchandise R Answers Online
Talk to our expert to get the help with ITECH 1006 Database Management Systems-Merchandise Stock Merchandise R 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.
