A First Course in Database Systems 연습문제 2장

본 글은 데이터베이스 수업을 들으며 시험을 위해 A First Course in Database Systems 연습문제를 풀이한 글입니다. 일개 학부생이 적은 글이므로 틀린 내용이 있을 수 있습니다. 만약 틀린 내용이 있으면 댓글이나 이메일 주시면 감사하겠습니다. 지적은 언제나 환영입니다. 

---

A First Course in Database Systems 책 사진

http://book.interpark.com/product/BookDisplay.do?_method=detail&sc.prdNo=257670552 

싸니까 믿으니까 인터파크도서

 

위 책의 연습문제를 정리한 내용입니다. 

p25 2.1, p33 3.1

 

연습문제 2.1

a) The attributes of each relation.

Accounts - acctNo, type, balance

Customers - firstName, lastName, idNo, account

 

b) The tuples of each relation.

Accounts - (12345, savings, 12000), (23456, checking, 1000), (34567, savings, 25),

Customers - (Robbie, Banks, 901-222, 12345), (Lena, Hand, 805-333, 12345),

(Lena, Hand, 805-333, 23456)

 

c) The components of one tuple from each relation.

Accounts - (acctNo : 12345, type : Savings, balance : 12000)

Customers - (firstName : Lena, lastName : Hand, idNo : 805-333, account : 12345)

 

d) The relation schema for each relation.

Accounts(acctNo, type, balance)

Customers(firstName, lastName, idNo, account)

 

e) The database schema.

Accounts(acctNo, type, balance), Customers(firstName, lastName, idNo, account)

 

f) A suitable domain for each attribute.

acctNo – integer, type – string, balance – integer,

firstName – string, lastName - string, idNo – string, account - integer

 

g) Another equivalent way to present each relation.

Accounts

accNo	balance	type
23456	1000	Checking
34567	25	Savings
12345	12000	Savings

Customers

idNo	account	firstName	lastName
805-333	23456	Lena	Hand
805-333	12345	Lena	Hand
901-222	12345	Robbie	Banks

 

연습문제 3.1

a) A suitable schema for relation Product.

CREATE TABLE Product(

    maker CHAR(25) NOTNULL,

    model CHAR(15) PRIMARY KEY,

    type CHAR(25) NOTNULL

);

 

b) A suitable schema for relation PC.

CREATE TABLE PC(

    model CHAR(15) PRIMARY KEY,

    speed DECIMAL (4,2) NOTNULL,

    ram INTEGER NOTNULL,

    hd INTEGER NOTNULL,

    price DECIMAL (7,2) NOTNULL

);

 

c) A suitable schema for relation Laptop.

CREATE TABLE Laptop(

    model CHAR(15) PRIMARY KEY,

    speed DECIMAL (4,2) NOTNULL,

    ram INTEGER NOTNULL,

    hd INTEGER NOTNULL,

    screen DECIMAL (3,1) NOTNULL,

    price DECIMAL (4,2) NOTNULL

);

 

d) A suitable schema for relation Printer.

CREATE TABLE Printer(

    model CHAR(15) PRIMARY KEY,

    color BOOLEAN NOTNULL,

    type CHAR(10) NOTNULL,

    price DECIMAL(7,2) NOTNULL

);

 

e) An alteration to your Printer schema from (d) to delete the attribute color.

답 :　ALTER TABLE Printer DROP color;

 

f) An alteration to your Laptop schema from (c) to add the attribute od(optical-disk type, e.g., cd or dvd). Let the default value for this attribute be ‘none’ if the laptop does bot have an optical disk.

답 :　ALTER TABLE Laptop ADD od CHAR(10) DEFAULT ‘none’