You are a database developer for a Games company. The company develops games. The company needs to store their games, customers, what games each customer buys, scores of each game that is played. The requirement analysis is given below. For your assignment you should; 1) Draw the ER diagram. 2) Create the relational database. 3) Create tables and make necessary relationships. Be sure to have created constraints. 4) Create a stored procedure to get a daily score report. This report should take parameters game id and the report date. It should display Id, first name, last name of the customer, the date and time and the score. 5)Create triggers for the 6th item of Requirement Analysis (RA). 6) Create a SQL server user for the 7th item of RA. 7) Create jobs for the 8th and 9th items of RA. Requirement Analysis 1) For each game name, first release date, current version, the category (action, strategy, etc.), the platform (mobile, Xbox, Playstation, etc.) should be stored. 2) For each customer first name, last name, nickname, registered date, country, preferred platform information should be stored. 3) The following information is necessary: which game has been bought by each customer, date of purchase and price. For instance, “customer A” bought “Mario” game on 10.01.2021 11:25 and paid 125 euro. 4) We need to know when the customers played the game and the score they reached after they played. For instance, on 11.01.2021 “customer A” played “Mario” at 12:10 and scored 120 points. 5) The total score of the customers for each game they bought should be stored. 6) Some items are sold in the games. The items are different in each game. For instance, diamonds and golds are sold in “Mario” and guns and shields are sold in “Call of Duty”. The items bought by each customer should be stored. The customers gain some points when they buy an item. The points differ from item to item. For instance, when they buy gold they earn 20 points or when they buy gold they earn 10 points. a. When the customer buys an item, the points should be automatically added to the total score of the customer for the current game. b. If the customer cancels the buying process of an item, the points should be removed from the total score of the customer. 7) The finance employee of our company should only see the data of which game has been bought by each customer, date of purchase and price information. He shouldn’t see data except the ones motioned. 8) The highest score for each game should be stored every day at 23:59automatically. If a problem occurs, the system should inform us via email (help@center.com). 9) Our database should be fully backed up every two days at 23:50 automatically.
You are a
1) Draw the ER diagram.
2) Create the relational database.
3) Create tables and make necessary relationships. Be sure to have created constraints.
4) Create a stored procedure to get a daily score report. This report should take parameters game id and the report date. It should display Id, first name, last name of the customer, the date and time and the score.
5)Create triggers for the 6th item of Requirement Analysis (RA).
6) Create a SQL server user for the 7th item of RA.
7) Create jobs for the 8th and 9th items of RA.
Requirement Analysis
1) For each game name, first release date, current version, the category (action, strategy, etc.), the platform (mobile, Xbox, Playstation, etc.) should be stored.
2) For each customer first name, last name, nickname, registered date, country, preferred platform information should be stored.
3) The following information is necessary: which game has been bought by each customer, date of purchase and price. For instance, “customer A” bought “Mario” game on 10.01.2021 11:25 and paid 125 euro.
4) We need to know when the customers played the game and the score they reached after they played. For instance, on 11.01.2021 “customer A” played “Mario” at 12:10 and scored 120 points.
5) The total score of the customers for each game they bought should be stored.
6) Some items are sold in the games. The items are different in each game. For instance, diamonds and golds are sold in “Mario” and guns and shields are sold in “Call of Duty”. The items bought by each customer should be stored. The customers gain some points when they buy an item. The points differ from item to item. For instance, when they buy gold they earn 20 points or when they buy gold they earn 10 points. a. When the customer buys an item, the points should be automatically added to the total score of the customer for the current game. b. If the customer cancels the buying process of an item, the points should be removed from the total score of the customer.
7) The finance employee of our company should only see the data of which game has been bought by each customer, date of purchase and price information. He shouldn’t see data except the ones motioned.
8) The highest score for each game should be stored every day at 23:59automatically. If a problem occurs, the system should inform us via email (help@center.com).
9) Our database should be fully backed up every two days at 23:50 automatically.
Trending now
This is a popular solution!
Step by step
Solved in 3 steps with 1 images
