4. (Total 10 points) Coursera hires you as a database analyst. You find that the database...

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).