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

Database Management Systems. Project Part 1 Solution Review 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 auto-pay, 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. COMP231: Database Management Systems. Project Part 1 Solution Review * 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. Plans [R1] Plan_code Name Type Desc Contract_period Fee * 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 auto-pay, his/her credit card information (i.e., card number, owner name, expiry date) must be stored. ID Name Contact_phone Billing_addr Card_no Owner Expiry_date Customers [R2] Credit_cards[R3] Assume that more than one customer can use same credit card for auto-pay Payment_method Constraint: Payment_method = ‘auto’ Pays_using[R3] ID Name Contact_phone Billing_addr Card_no Owner Expi