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
-
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...
-
Discuss the challenges marketers face with international market segmentation?
-
An accounting student asked the following question: Why does the cash on hand balance as at the end of December 2016 in the statement of cash flow not concur with the cash balance shown in current...
-
Determine the sidelighting workplace illuminances for a \(20 \mathrm{ft}\) long, \(15 \mathrm{ft}\) wide (deep), \(8 \mathrm{ft}\) high light-colored room with a \(15 \mathrm{ft}\) long by \(5...
-
Assuming that all materials are added at the beginning of the process and the labor and factory overhead are applied evenly during the process, compute the figures to be inserted in the blank spaces...
-
At April 30, Blossom Company has the following bank information: Cash balance per bank $8100 Outstanding checks $500 Deposits in transit $975 Credit memo for interest $20 Bank service charge $40....
-
ToyWorks Ltd. is a company that manufactures and sells a single product, which they call a Toodle. For planning and control purposes they utilize a monthly master budget, which is usually developed...
-
First, familiarize yourself with the definition of unemployment, and the different classifications of unemployment (cyclical, frictional, structural) Interview five (5) different people of your...
-
Elizabeth Styles commenced a new interior styling business, called Coastal Styling. For the purposes of this activity, the business is not registered for GST. The business commenced on 1 June 2021,...
-
Abnormal wastage per unit 50 Selling price per unit? 1.2 There were 1 500 units of finished products on hand at 31 December 2019. 1.3 The selling agents' commission is 10% of the selling price. 1.4...
-
You, CPA, work in the tax department of a small public accounting firm in Edmonton, Alberta. The busy tax season has just finished and on a brisk Monday morning in May the partner introduces you to...
-
Determine Return on assets for 2020,2019 & 2018. Return on Assets=NI + {Interest Expense * (1-Tax)} / Avg TA Acid-test ratio (CA-inventory-prepaid)/CL Current ratio (CA/CL) Debt ratio Earnings per...
-
manufacturer costs: Purchasing Handling materials Machine setups Inspections Utilities Cost Driver Orders Material moves Machine setups Number of inspections Square feet $70,000 31,333 70,500 25.500...
-
Happy Feet Ltd. is the sole distributor of a popular brand of running shoes to local sporting stores in Southern Ontario. Happy Feet does not sell any products other than the single type of running...
-
Write a paper about medication error system 2016.
-
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.
-
Does the fact that selling is included as part of the promotional mix weaken or strengthen its role as a sub-element of marketing?
-
Discuss the role and function of selling.
-
Comment on why the proposed changes to sales processes are necessary. Also, which changes would you recommend in terms of their: (a) selling activities? (b) sales value proposition?
Study smarter with the SolutionInn App