数据库1

名词解释

Database Management System：数据库管理系统，是一种操纵和管理数据库的大型软件，用于建立、使用和维护数据库，简称dbms

Data Model：用于描述数据或信息的标记

View：由其他关系上的查询所定义的一种关系，并不在数据库中储存，但可以对它进行查询，就像它确实被存储在数据库中一样。

UML：统一建模语言，最初是用来在面向对象风格中作为描述软件设计的一种图形化标注，现在也作为一种流行的数据库设计描述。

Functional Dependency：函数依赖，是指如果R的两个元组在属性A1，A2，……A n上一致，那么它们必定在其他属性B1，B2……B m上也一致。

Referential Integrity Constraint：参照完整性约束，一个实体集中的某一个或某一些属性，在本实体集中不为key，但在其他实体集中为key，要么为空，要么为被参照对象对应取之。BCNF：在一个简单的条件下保证异常不存在/

Weak Entity Set：弱实体集，一个实体集键是由另一个实体集的部分或全部属性构成。Projection：投影：用来从关系R生成一个新的关系，这个关系只包含原来关系R中的部分列。

最后一题

Show how to alter your relation schemas for the movie example, in the following ways.

Movie(title, year, length, inColor, studioName, producerC#)

MovieStar(name, address, gender, birthdate)

MovieExec(name, address, cert#, netWorth)

a) Make title and year the key for Movie.

ALTER TABLE Movie ADD CONSTRAINT MovieKey PRIMARY KEY(title,year);

b) Require that no name appear as both a movie star and movie executive (this constraint need not be maintained in the face of deleteions).

ALTER TABLE Moviestar ADD CONSTRAINT c1

CHECK(name NOT IN(SELECT name FROM MovieExec))

ALTER TABLE MovieExec ADD CONSTRAINT c2

CHECK(name NOT IN(SELECT name FROM Moviestar))

2.R(A,B):{(0,1),(2,3),(0,1),(2,4),(3,4)}

S(B,C):{(0,1),(2,4),(2,5),(3,4),(0,2),(3,4)}

3.Write the SQL statements,based on the following database schema.

Product (maker, model, type)

PC (model, speed, ram, hd, rd, price)

Laptop (model, speed, ram, hd, screen, price)

1) Give suitable declarations for the relation PC.

2) Give the manufacturer(maker) and speed of laptops with a hard disk of at least thirty gigabytes.

3) Delete all PC’s with less than 128 megabytes of RAM.

4)An alteration to your Laptop schema to add the attribute od(optical-disk)

1）CREATE TABLE PC(

Model CHAR(30),

speed DECIMAL(4,2)

ram INTEGER ,

hd INTEGER ,

price DECIMAL(7,2)

);

2）SELECT maker ,speed

FROM Product ,Laptop

WHERE hd>30 and product.model =Laptop.model

3)DELETE FROM PC

WHERE RAM<128

4)ALTER TABLE Laptop ADD od CHAR(10)

4.关键字employee,project

a)Draw E/R diagram

b)convert to relational database schema employee(employeeID,name,address,birthdate) project(projectName,startdate)

assign(billingrate,employeeID ,projectName)

c) )Render the database in ODL

class employee{

attributr string employeeID;

attributr string name;

attributr string address;

attributr DATE birthdate;

relationship Set projects

inverse project assign;

};

class project{

attribute string project name;

attribute DATE startdate;

relationship set assign

inverse employee projects

};

d )Given suitabl

e daclaretions for one relation

CREATE TABLE project(

projectname CHAR(30) PRIMARY KEY ,

startdate DATE

);

5.Design a database for a bank, including information about customers and their accounts….. a

)Draw E/R diagram