4. (Total 10 points) Coursera hires you as a database analyst. You find that the database...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
4. (Total 10 points) Coursera hires you as a database analyst. You find that the database structure of Coursera is outdated. So, you become a team leader for the new database project. Now, you want to start to modify the simplest database tracking customers and classes. To track your customers' information, Coursera should have a customer profile such as a Username, password, name of the customer, email, and member type (Premium, Regular, Trial, and Student). Each of the customers must store at least one credit card information, including credit card number, and credit card type such as VISA, Master, American Express, etc. (You can have an entire new entity called card type. But, in this case, you will not consider the option.), expiration month, expiration year, and 3 digits of security code. (Coursera does not accept the family card.) Each of the customers can study as many online classes as possible, while the customer does not need to have any courses to take. Each online class has its ID and name of the class. Multiple classes would have the same name, but the class ID should be different. In the online class information, you should be able to track the member's type who can attend the class. Sometimes, one online class is available to many different types of members. (1) (3 points) Create the entity relationship diagram for an appropriate data model. Be sure to indicate the identifier (Primary keys and foreign keys) for each entity (if any) and the relationships between entities using the crow's-feet method. (HINT) For the first step of your ER Diagram, you should have four entities. But, if you do not normalize those, you will get 1 point or less. (2) (2 point) Create an Entity Data Model (Entities only) with two fake data in each entity. (3) (2 point) Please write a SQL code to print the names of customers and member types when the customer's card is VISA. (4) (2 points) Display a Cartesian product table using the fictitious data generated from Q4(2), which represents the outcomes of Q4(3). (5) (1 point) Present the result table obtained from the SQL query in Q4(3) utilizing the fabricated data generated in Q4(2). 4. (Total 10 points) Coursera hires you as a database analyst. You find that the database structure of Coursera is outdated. So, you become a team leader for the new database project. Now, you want to start to modify the simplest database tracking customers and classes. To track your customers' information, Coursera should have a customer profile such as a Username, password, name of the customer, email, and member type (Premium, Regular, Trial, and Student). Each of the customers must store at least one credit card information, including credit card number, and credit card type such as VISA, Master, American Express, etc. (You can have an entire new entity called card type. But, in this case, you will not consider the option.), expiration month, expiration year, and 3 digits of security code. (Coursera does not accept the family card.) Each of the customers can study as many online classes as possible, while the customer does not need to have any courses to take. Each online class has its ID and name of the class. Multiple classes would have the same name, but the class ID should be different. In the online class information, you should be able to track the member's type who can attend the class. Sometimes, one online class is available to many different types of members. (1) (3 points) Create the entity relationship diagram for an appropriate data model. Be sure to indicate the identifier (Primary keys and foreign keys) for each entity (if any) and the relationships between entities using the crow's-feet method. (HINT) For the first step of your ER Diagram, you should have four entities. But, if you do not normalize those, you will get 1 point or less. (2) (2 point) Create an Entity Data Model (Entities only) with two fake data in each entity. (3) (2 point) Please write a SQL code to print the names of customers and member types when the customer's card is VISA. (4) (2 points) Display a Cartesian product table using the fictitious data generated from Q4(2), which represents the outcomes of Q4(3). (5) (1 point) Present the result table obtained from the SQL query in Q4(3) utilizing the fabricated data generated in Q4(2).
Expert Answer:
Answer rating: 100% (QA)
1 Entity Relationship Diagram Customer CustomerID Primary Key Username Password Name Email MemberTyp... View the full answer
Related Book For
Posted Date:
Students also viewed these programming questions
-
Bill Handy, The finance director of Northern Australia Global Investments Ltd (NAGIL), is unsure whether he should consolidate some of the investments that the company owns. He has asked your advice...
-
Planning is one of the most important management functions in any business. A front office managers first step in planning should involve determine the departments goals. Planning also includes...
-
Googles ease of use and superior search results have propelled the search engine to its num- ber one status, ousting the early dominance of competitors such as WebCrawler and Infos- eek. Even later...
-
ABC Pty Ltd would like to set up a Virtualisation Platform on their organisation. You have been hired by Company to be their network and system administrator to implement virtualisation for...
-
Impulses that move doom the T system of a muscle fiber most directly cause a. Movement of tropomyosin. b. Attachment of the cross bridges to myosin. c. Release of Ca2+ from the sarcoplasmic...
-
Thai Bays computer system generated the following trial balance on December 31, 2011. The companys manager knows something is wrong with the trial balance because it does not show any balance for...
-
Question: Begg & Daigle, Inc., wrote a check for $31,989.80 to Newwall Interior Partitions, Inc. Begg then asked Chemical Bank to stop payment on the check. In November, the bank accidentally paid...
-
The Carbondale Hospital is considering the purchase of a new ambulance. The decision will rest partly on the anticipated mileage to be drive next year. The miles driven during the past 5 years are as...
-
The T-accounts for Equipment and the related Accumulated Depreciation-Equipment for Grouper Company at the end of 2022 are shown here. Equipment Beg. bal. 80,600 Disposals 23,800 Acquisitions 40,000...
-
TV Trends to Watch in 2021 In 2021, expect larger and cheaper 4K TVs, a new level of voice control, interaction with other smart products, and mini Led backlights to improve color contrast and...
-
Green Cast, Inc. produces and sells a line of ovenware (Oven-Safe Classic) that goes from oven (conventional or microwave) to the refrigerator without breaking. Its design is functional, yet...
-
In the third department of a company, a portion of the materials (a by-product) is removed, further processes and sold. The company uses reversal cost method to account for the by-product. Data for...
-
(i)What is your dollar profit if the stock price in June is $70? What if it is $50? June with a strike price of $65. (3 Marks) Suppose you buy a call option for $1.65 that matures in June with a...
-
Consider a bank where loan applications are first handled by a credit analyst, and then by a loan officer. The credit analyst takes32 minutes for each application, and the loan officer takes6minutes...
-
Anthony Co. has provided the following information Using the FIFO method of process costing, complete each of the following: a. How many units were completed and transferred to Finished Goods? b....
-
why should management accountants create extra work for the organization by deliberately adopting policies for imternal costing thats violates the generally accounting principles
-
Brand Elements of Logos and Symbols of ALBAIK restaurant in Saudi Arabia: Transferability: Adaptability: Protectability:
-
During registration at Tech every quarter, students in the Department of Management must have their courses approved by the departmental advisor. It takes the advisor an average of 4 minutes...
-
Why does price leadership sometimes evolve in oligopolistic markets? Explain how the price leader determines a profit-maximizing price.
-
In the context of our analysis of the Edgeworth production box, suppose that a new invention changes a constant-returns-to-scale food production process into one that exhibits sharply increasing...
-
If Jane is currently willing to trade 4 movie tickets for 1 basketball ticket, then she must like basketball better than movies. True or false? Explain.
-
Turbo Corporation had net income of \(\$ 30,000\) and other comprehensive income of \(\$ 0\) prior to the following two adjustments: Turbo Corporation discovered it has an unrealized holding loss of...
-
Lance Co. purchased 100 shares of Mallard common stock at \$20 per share on March 15, 2020. Mallard declared and paid a dividend of \(\$ 1.50\) per share in 2020. The market price on December 31,...
-
An investor purchased 100 shares of Mallard common stock at \(\$ 20\) per share on March 15, 2020. On December 31, 2020, the stock was quoted at \(\$ 19\) per share and declared and paid a dividend...
Study smarter with the SolutionInn App