数据库ER图练习及答案

更新时间:2023-06-16 18:51:42 阅读：评论：0

如何提升进店率

DB Modeling Exam Practical

Answer the following questions.

1.Create an E-R schema for a databa system ud to manage account information at a community bank. The bank has veral branches with unique names. A customer may have one or more accounts in one or more branches. An account must belong to one and only one branch. Each account is operated on by transactions, which may be deposits to or withdrawals from some account. The databa keeps track of all the transactions on each account, in addition to the balance of individual accounts and the asts of individual branches. For each entity, specify all its attributes, primary key, and alternate key(s). In your ER schema, be sure to capture the cardinality constraints and participation constraints of all relationships.

Make reasonable assumptions to complete the specification. Explicitly state all your assumptions. EVERY construct in your ER schema should be substantiated by either the specification above or your explicit assumptions.

2.The following table stores information about which suppliers can supply which parts. The table captures the fact that a part who name is PartName and who ID is PartID can be supplied by suppliers who names are in SupplierName and who IDs are in SupplierID. Note that a part can be supplied by many suppliers, and a supplier can supply many parts.

CAN_SUPPLY

PartID	PartName	SupplierID	SupplierName
1234	Nut	223communistparty	wordartProMetal
1234	Nutcpa是什么	224	Biscayne
2134	Bolt	223	ProMetal

3.阻抑Perform the following tasks.

1.List the primary key.

2.List all the FDs.

upon a christmas night

3.What normal form is the relation in Explain.

4.Apply normalization to it incrementally, carrying the normalization process through each of the higher normal forms possible up to 3NF. That is, if the relation were unnormalized, bring it to first normal form, then bring the first normal form you've just created to cond normal form, and then bring the cond normal form to third normal form.

For each transformation to the next higher normal form X,

Explain the steps you took to bring it to the normal form X.

Provide the normal form X's table structure, primary key(s), and the FDs.

Explain why you think it is in the normal form X. For example, if you think there is a partial dependency, fully defend your conclusion by explaining how a column is partially dependent on some other column(s).

That is, if the relation were in an unnormalized form, you would explain the transformation you performed to bring it to first, cond, and third normal forms. You would also provide the table structure, the primary key and the FDs for the first, cond, and third normal forms. You would also provide explanation for why you believe it is in first, cond, and third normal forms.

4.Convert the following E-R schema into a relational schema using the mapping algorithm specified in this cour. Specify key and referential integrity constraints, using directed arcs. Make sure you also identify alternate keys. Label each step of the mapping algorithm.

Answer:

Entity:

1.Bank(BankName,BankPhone) (BankPhone is a multi-valued attribute.)

PK: (BankName)

2.Cutomer(CustID, CustName, PhoneNum)

PK: (CustID)

AK: (PhoneNum)

3.Branch (BranchName, BranchAddr, BranchPhone, Asts) (BranchPhone is a multi-valued attribute.)

PK: ( BranchName)

4.Account (AccountNo, Balance)

PK: (AccountNo)

5.Transaction (TID, OperationType, TDateTime)

PK: (TID)

Relations:

1.Has: <Bank, Branch>, 1:N, PARTIAL/ TOTAL;

2.Open: <Customer, Account>, 1:N, PARTIAL/ TOTAL;

3.AofBranch: <Branch, Account>, 1:N, 分隔符PARTIAL/ TOTAL;

4.TofAccount: <Account, Transaction>, 1:N, PARTIAL/ TOTAL;

Assumptions:

1.A new bank can establish no branch.

2.One normal bank establishes one or more braches.

3.A bank has one or more telephones for customers.

4.lacros是什么意思A customer can open one or more Account.

5.An account must belong to one and only one branch.

6.One branch opens one or more accounts.

7.A branch has one or more telephones for customers.

8.An account belongs to just one branch;

how about2.

:(PartID, SupplierID)

FD1: PartID->{PartName}

FD2: SupplierID->{SupplierName}

3. The relation is in the first normal form(1NF).

Each attribute of the relation allows a single atomic value, so it is in 1NF.

But some none-primary-key attributes, such as PartName and SupplierName, partially

dependant on the primary key (as FD1 and FD2 show), so it is not in 2NF.

4. Normalization:

1) FD1: PartID->{PartName}

The relation can be decomposited into two relations:

PART(PartID, PartName),FDs={PartID->PartName}, PK:(PartID);

CAN_SUPLY(PartID, SupplierID, SuplierName), FDs={SupplierID->SupplierName), PK:(PartID,SupplierID).

The relation PART is now in the third normal form becau the only none-primary-key

attribute PartNameyanyu, fully (not partially) and directly (not transively) dependants on the pri

mary key PartId.

The relation CAN_SUPPLY is still in the first normal becau the only none-primary-key

attribute SupplierName, partially dependants on the primary key (PartId,SupplierID).

2)CAN_SUPLY(PartID, SupplierID, SuplierName), FDs={SupplierID->SuplierName):

For SupplierID->SuplierName, the relation can be decomposited into two relations:

SUPPLIER(SupplierID,SupplierName),FDs={SupplierID->SuplierName}, PK:(SupplierID);

CAN_SUPLY(PartID, SupplierID), FDs={}, PK:(PartID,SupplierID).

Both relations are in the third normal form, becau for each one, no none-primary-key

attribute patially or transively dependants on its primary key.

3) Three 3NF relations:

PART(PartID, PartName),FDs={PartID->PartName}, PK:(PartID);

本文发布于:2023-06-16 18:51:42，感谢您对本站的认可！

本文链接：https://www.wtabcd.cn/fanwen/fan/90/147284.html

上一篇：酒店部门及酒店职位英文翻译大全（推荐阅读）

下一篇：TikTokAPI接口，关键词搜索用户

标签：提升店率

留言与评论（共有 0 条评论）