3 parts.txt

ssd7考试2的答案

Part1:

Entities:
1. BANK: BankID, BankName
2. BRANCH: BrID,BrName,BrAdd
3. ACCOUNT: AccountID,Balance,InitialTime
4. CUSTOMER: CID,CName,CAdd,Tel

Relationships:
1. HAS :< BANK, BRANCH >, 1: N, TOTAL/PARTIAL
2. BELONG To:< ACCOUNT, BRANCH >, N: 1, TOTAL/PARTIAL
3. Own :< CUSTOMER, ACCOUNT >, 1: N, TOTAL/TOTAL,Date,Type,Value

Some Assumptions:
1. A bank may have zero or more branches,every branch has unique BranchID. 
2. A customer may have one or more accounts in one or more branches.
3. An account must belong to one and only one branch.
4. Customers own their accounts, they may withdraw or deposit their accounts. 

Part2:

1). Primary key:
	(PartID，SupplierID)

2). FDs
	 PartID->PartName
	 SupplierID->SupplierName
	 (PartID, SupplierID)->PartName
	 (PartID, SupplierID)->SupplierName

3). The relation is in 1NF. Because every attribute is a single atomic value and the non-primary-key attributes, PartName and SupplierName is partial functionally dependent on the primary key (PartID, SupplierID). Then we can see the relation is in 1NF but not in 2NF.

4). Devide the table into three tables in order to eliminate the Partial Dependency of non-primary-key attributes. Then the relation will be in 2NF. It shows as below.

R1: (PartID,PartName), primary key is PartID, FD: PartID->PartName

	PartID	PartName
	1234  	Nut   
	2134  	Bolt  

R2: (SupplierID,SupplierName) ,primary key is SupplierID, FD: SupplierID->SupplierName
	SupplierID	SupplierName
	223    		ProMetal   
	224    		Biscayne   

R3: (PartID, SupplierID), primary key is (PartID, SupplierID), There is no Function Dependency in this relation.
PartID	SupplierID
1234  	223    
1234  	224    
2134  	223    

There is not any Partial Dependency or Transitive Dependency in the relations, so these three ralations are in 3NF.

Part3:

1)
COACH(Cname, Cage)
primary key:Cname 

TEAM(Tname)
primary key:Tname

GAME (Number, Date, Score, Time)
primary key:Number

PLAYER (Pname, age)
primary key:Pname

STADIUM (Sname, size, Location)
primary key:Sname 

COLOR(ColorID,Color1,Color2,Color3)
primary key:ColorID
***********************************************************
2)
PRACTICE (date)

***********************************************************
3)
TEAM-PLAYER
PLAYER (Pname, age, Tname)
primary key:Pname
foreign key:Tname --> TEAM(Tname)


TEAM(H)-GAME TEAM(V)-GAME
GAME (Number, HostTeam, VisitorTeam, Date, Score, Time)
primary key:Number, alternate key: (HostTeam, VisitorTeam)
foreign key:HostTeam-->Team(Tname)
foreign key:VisitorTeam --> Team(Tname)

***********************************************************
4)
TEAM COACH 1:1
TEAM(Tname, Cname, Cage)
primary key:Tname
alternate key:Cname

***********************************************************
5)
TEAM(H) TEAM(V) 1:1
PLAYWITH(HostTeam, VisitorTeam)
primary key:HostTeam, VisitorTeam
primary key:HostTeam --> TEAM(Tname)
primary key:VisitorTeam --> TEAM(Tname)

***********************************************************
6)
Null

***********************************************************
7)
PRACTICE (Tname, Sname, date)
primary key:Tname, Sname
foreign key:Tname --> TEAM(Tname)
froeign key:Sname --> STADIUM (Sname)

***********************************************************
8)
Null

***********************************************************
9)

TEAMCOLOR(Tname, ColorID, Color1, Color2, Color3)
primay key:ColorID
foreign key:Tname --> TEA