东北大学信息安全数据库实验作业答案

Type Name Plan_code
Desc Contract_period Fee
Plans
[R1]
COMP231: Database Management Systems. Project Part 1 Solution Review
3
ER Diagram [R2], [R3]
• [R2]: For each customer, we create a record with his/her ID, full name, contact phone number, billing address, payment method. • [R3]: Every customer must select one payment method for all his subscriptions – cash or auto-pay using credit card. If the customer uses autopay, his/her credit card information (i.e., card number, owner name, expiry date) must be stored. Owner
[R2]
Card_no
Pays_ using
[R3] Constraint: Payment_method = ‘auto’
> Credit_cards [R3]
Assume that a credit card can be used by a single customer
ER Diagram [R4], [R5],[R6], [R7]
7
Plan_code Name Type Desc Fee Contract_ period
Complete ER Diagram
Plans
Associates _to Email Sub_ID
BB_Subs Subscriptions
ISA disjoint
Password
Act_date Installation_ addr
) •
)
9
Create.sql (2/2)
• Subscriptions table:
create table BB ( sub_id char(8), act_date date not null, installation_addr char(100) not null, email char(8) unique, password char(8), ID char(8), plan_code char(4), primary key(sub_id), foreign key(ID)references customers, foreign key(plan_code) reference plans, )
6
ER Diagram [R8]
• [R8a,b] A customer subscribes to at least a service plan.
Plans
Subscriptions
[R4]
Associates_to
[R8b]
Customers
[R2]
<
Makes
[R8a]
COMP231: Database Management Systems. Project Part 1 Solution Review
Expiry_date Card_no Billing_addr Customers Contact_phone Name ID Assume that more than one customer can use same credit card for auto-pay Payment_method
HT_Subs
Phone
<
Billing_ addr Contact_ phone Name ID Payment_ method
Makes
Owner
Expiry_date
Customers
Credit_card
Card_no Pays_ using [R3] Credit_cards [R3]
>
8
10
Create.sql (2/2)
• Subscriptions table:
create table HT ( sub_id char(8), act_date date not null, installation_addr char(100) not null, phone char(12) unique, ID char(8), plan_code char(4), primary key(sub_id), foreign key(ID)references customers, foreign key(plan_code) reference plans, )
Email
[R6]
Sub_ID
[R5]
BB_Subs
[R5]
Password
[R6]
Act_date Installation_addr
Subscriptions
[R4]

ISA
disjoint
HT_Subs
[R5]
Phone
[R7]
COMP231: Database Management Systems. Project Part 1 Solution Review
11
Create.sql (1/2)
• Plans table: create table plans ( plan_code char(4), name char(20) not null, type char(2) not null, description char(100) not null, fee number(3) not null, contract_period char(2) not null, primary key(plaln_code), ) • Customers table: create table customers ( ID char(8) name char(20) not null, cont_phone number(8) not null, bill_addr char(100) not null, payment_method char(4) not null, primary key(ID), Credit Card table: create table credit( credit_card_nochar(16), owner char(20), expiry_date date, ID char(8), primary key(credit_card_no), foreign key(ID) reference customers,
Project Part 1 Solution Review (20’’)
Requirements
• [R1]: For each service plan, we need to keep the plan code, name, service type (BB or HT), description (…), contract period and monthly fee. • [R2]: For each customer, we create a record with his/her ID, full name, contact phone number, billing address, payment method. • [R3]: Every customer must select one payment method for all his subscriptions – cash or auto-pay using credit card. If the customer uses autopay, his/her credit card information (i.e., card number, owner name, expiry date) must be stored. • [R4]: A subscription has a subscription ID, activation date and installation address and belongs to a single customer. • [R5]: Each subscription is either a BB plan or HT plan subscription, but not both. • [R6]: A BB plan subscription has additional email and password attributes. • [R7]: A HT plan subscription has additional phone number attribute. • [R8]: A customer subscribes to at least a service plan.
2
ER Diagram [R1]
• [R1]: For each service plan, we need to keep the plan code, name, service type (BB or HT), description (…), contract period and monthly fee.
4
[R2] [R3] Constraint: Payment_method = ‘auto’
<
Pays_ using
Credit_cards
[R3]
Owner Expiry_date Billing_addr Contact_phone Name ID Payment_method Customers