[Solved]Activity 1 50 Pts Database Design 20 Pts Draw E R Diagram Application B 10 Pts Write Const Q37282083
Activity 1 (50 pts.): Database Design a) (20 pts.) Draw the E-R diagram for this application. b) (10 pts.) Write down all the constraints of this scenario. Justify why you added those constraints. c) (20 pts.) Reduce your E-R diagram to an appropriate set of relational schemas. Write down any constraints that you were able to model with the E-R model, but that cannot be modeled with the relational model Suppose that you are contacted by a manager of an online discount broker like Charles Schwab to design them a new database for their business. After talking with the manager., you were able to elicit the following information about their business: For every Schwab client, the company keeps track of the SSN, name, birthdate, address (city and state), phone(s) and email o Every Schwab client has at least one account and can have several accounts. Each account is identified by its account number. All accounts also have a balance and an opening date. Balances need to be greater than zero Accounts can be either Standard Accounts or IRA accounts, but cannot be of both types simultaneously. A client can have at most one IRA account. . Clients can either deposit or withdraw money from their accounts. For each deposit/withdrawal, Schwab records the date of the transaction and the amount involved » A client cannot deposit more than $6,000 dollars per calendar year in his/her IRA account. » Each account can have zero or more securities. Each security is uniquely identified by its CUSIP number. Every security has a current bid price, a current ask price and a yield (a number) Each security is either a Stock or a Bond, but cannot be both at the same time. Stocks have ticker symbols (a string that can also be used to identify the stock), while bonds have the name company that issued them, a coupon rate and a maturity date Clients can make transactions with their accounts. Transactions can be of two types: buy or sell. In each of these transactions, a client can either buy or sell (but not both » . in the same transaction) securities from one of his/her accounts. A transaction involves only one CUSIP. For example, you can sell 1,000 stocks of “Johnson & Johnson” (CUSIP 10) in one transaction, but you cannot sell 500 stocks of “Johnson & Johnson” (CUSIP 10) and 500 stocks of “Pfizer” (CUSIP 34) in the same transaction, because this involves two different CUSIPS. 29 For each buy/sell transaction, Schwab records the date of the transaction, the CUSIP of the security involved, the number of securities traded, the cost of the transaction, the SSN of the buyer and the SSN of the seller Show transcribed image text Activity 1 (50 pts.): Database Design a) (20 pts.) Draw the E-R diagram for this application. b) (10 pts.) Write down all the constraints of this scenario. Justify why you added those constraints. c) (20 pts.) Reduce your E-R diagram to an appropriate set of relational schemas. Write down any constraints that you were able to model with the E-R model, but that cannot be modeled with the relational model
Suppose that you are contacted by a manager of an online discount broker like Charles Schwab to design them a new database for their business. After talking with the manager., you were able to elicit the following information about their business: For every Schwab client, the company keeps track of the SSN, name, birthdate, address (city and state), phone(s) and email o Every Schwab client has at least one account and can have several accounts. Each account is identified by its account number. All accounts also have a balance and an opening date. Balances need to be greater than zero Accounts can be either Standard Accounts or IRA accounts, but cannot be of both types simultaneously. A client can have at most one IRA account. . Clients can either deposit or withdraw money from their accounts. For each deposit/withdrawal, Schwab records the date of the transaction and the amount involved » A client cannot deposit more than $6,000 dollars per calendar year in his/her IRA account. » Each account can have zero or more securities. Each security is uniquely identified by its CUSIP number. Every security has a current bid price, a current ask price and a yield (a number) Each security is either a Stock or a Bond, but cannot be both at the same time. Stocks have ticker symbols (a string that can also be used to identify the stock), while bonds have the name company that issued them, a coupon rate and a maturity date Clients can make transactions with their accounts. Transactions can be of two types: buy or sell. In each of these transactions, a client can either buy or sell (but not both » .
in the same transaction) securities from one of his/her accounts. A transaction involves only one CUSIP. For example, you can sell 1,000 stocks of “Johnson & Johnson” (CUSIP 10) in one transaction, but you cannot sell 500 stocks of “Johnson & Johnson” (CUSIP 10) and 500 stocks of “Pfizer” (CUSIP 34) in the same transaction, because this involves two different CUSIPS. 29 For each buy/sell transaction, Schwab records the date of the transaction, the CUSIP of the security involved, the number of securities traded, the cost of the transaction, the SSN of the buyer and the SSN of the seller
Expert Answer
Answer to Activity 1 (50 pts.): Database Design a) (20 pts.) Draw the E-R diagram for this application. b) (10 pts.) Write down al… . . .
OR
