Suppose you are given the following requirements for a simple database for the National Hockey League (NHL): • There exists many teams in the NHL • Each team has a teamID, name, a city, a coach, and one or more players • Each player belongs to only one team Each player has a playerID, name, a position (such as left wing or goalie), a skill level, and a set of zero or more injury records • An injury record has an injuryRecordID, a game, a player, and a description. • A game is played between two teams and has a gameID, a home TeamID, an away TeamID, a date (such asMay 11th, 2017), a home team score, and an away team score. Construct a schema for this database and ensure all relationships cardinalities (1:1, 1:M, M:M) match the requirements above. Then, using this schema, write your Data Definition Language queries in SQL to be able to create these tables in SQLite. Ensure all relevant constraints (Primary Keys, Foreign Keys, NOT NULL, data types) are included. Submit both the schema diagram and a .sql file containing all of your create table queries.

Suppose you are given the following requirements for a simple database for the National Hockey League (NHL): • There exists many teams in the NHL • Each team has a teamID, name, a city, a coach, and one or more players • Each player belongs to only one team Each player has a playerID, name, a position (such as left wing or goalie), a skill level, and a set of zero or more injury records • An injury record has an injuryRecordID, a game, a player, and a description. • A game is played between two teams and has a gameID, a home TeamID, an away TeamID, a date (such asMay 11th, 2017), a home team score, and an away team score. Construct a schema for this database and ensure all relationships cardinalities (1:1, 1:M, M:M) match the requirements above. Then, using this schema, write your Data Definition Language queries in SQL to be able to create these tables in SQLite. Ensure all relevant constraints (Primary Keys, Foreign Keys, NOT NULL, data types) are included. Submit both the schema diagram and a .sql file containing all of your create table queries.

Database System Concepts

7th Edition

ISBN:9780078022159

Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan

Chapter1: Introduction

Section: Chapter Questions

Problem 1PE

See similar textbooks

Similar questions

Although you always wanted to be a dog trainer, you ended up being an expert on databases because you love to work with data and logic. Your old love is still there, however, so you set up a database company, DogTraining, that builds a product for dog kennels. The core of this product is a database with a schema that captures all the information that kennels need to maintain. Kennels keep information about dog trainers, the First and Last names of the dog trainers (which are unique), birthplace, color of the dog trainers hair, and style of training. For each dog, the trainer, the date the dog was born, a name (not unique), the type of dog (e.g., Brittany, German Shepard, Collie, etc.), color of the dog’s hair, and how much weighs must be stored. Dogs are also classified into groups of various kinds, for example, hunting, guard, working; a given dog may belong to more than one group. Each group is identified by a name (like those just given) that describes the group. Finally, kennels…
The problems for this chapter use a database for a simple department store that sells items to customers and wants to keep track of the invoices, the selling price (if an item is on sale), and the sales tax (7%) to be collected on some items. Every customer and invoice are assigned unique numbers. All items have a Universal Product Code (UPC) number and bar-code assigned to each unique item. Food and non-carbonated beverages are not taxed, but clothing, home goods, and most other items are.
Database Design Before essential oil products are marketed, testing is carried out based on the production batch of each essential oil. This is done to maintain the quality and content of the constituents in the essential oil. Where the Test Percentage Content must reach the Minimum Percentage Content standard for each constituent. If there is one type of constituent that does not meet the minimum standards, the batch will not be marketed because it does not meet the quality standard. 1. If recording the results of each testing process is still done manually, in your opinion, please explain which approach is suitable between Centralized and View Integration for collecting requirements. 2. What Fact Finding methods will you choose to obtain information related to the development of a database system to record the testing process, and explain the reasons for choosing it. Note - Please type the answer and don't write it by hand because it's hard to read
An E-learning website needs your help to design its database. They need to store information about teachers and online tutorials. Each teacher has a unique ID, name, and at least one field of specialty. Teachers form groups to prepare online tutorials. Each group has unique name and at least one teacher as a member. Each online tutorial has a title, date and organized by exactly one group. The title of the tutorial is unique for the group. Each tutorial consists of several sessions, described by their titles and durations. Naturally, each session is presented by one teacher and belongs to exactly one tutorial. In addition, sessions on the same tutorial have different titles. Design an ERD for this application. Note any unspecified requirements, and make appropriate assumptions to make the specification complete.
Suppose we have a database for an university. The requirements are as follows: • Professors have an SSN, a name, an age, a rank, and a research specialty. • Projects have a project number, a sponsor name (e.g., NSF), a starting date, an ending date, and a budget. • Graduate students have an SSN, a name, an age, and a degree program (e.g., M.S. or Ph.D.). • Each project is managed by one professor (known as the project’s principal investigator). • Each project is worked on by one or more professors (known as the project’s co-investigators). • Professors can manage and/or work on multiple projects. • Each project is worked on by one or more graduate students (known as the project’s research assistants). • When graduate students work on a project, a professor must supervise their work on the project. Graduate students can work on multiple projects, in which case they will have a (potentially different) supervisor for each one. The FDs for the above requirements are given as, • PSSN →…
Using the Crow’s Foot Diagrram technique, draw a database design for the attached database.
DATABASE SYSTEMS: QUESTION: A local businesswoman has decided to start her own Internet business, called Masterpieces Ltd, hiring paintings to private individuals and commercial companies. Because of your reputation as a database designer she has called upon your services to design and implement a database to support her new business. At the initial planning meeting, to discuss the design, the following user requirements were requested. The system must be able to manage the details of customers, paintings and those paintings currently on hire to customers. Customers are categorized as B (bronze), S (silver), G (gold) or P (platinum). These categories entitle a customer to a discount of 0%, 5%, 10% or 15% respectively. Customers often request paintings by a particular artist or theme (e.g. animal, landscape, seascape, naval, still‐life, etc). Over time a customer may hire the same painting more than once. Each painting is allocated a customer monthly rental price defined by the owner.…
Create an ER diagram for the following requirements:You are asked to design a database to store music album information. A simplified set of requirementsare provided below.- A music album has a title, year of release, total running time, one or more artists, and list ofsongs.- Each song has a lead artist and may have a list of additional artists. Each song has a list of otherassociated people such as mixer, mix engineer, keyboards, guitar, etc. Each song has a genre,running time, and lyrics. Each song can be associated with many albums, but needs to beassociated with at least one.- Each person has a first name, last name, and nickname.
pls help You have been hired by a doctors office to analyze the current state and determine requirements for a Patient booking system. In creating models that reflect the current state you draw a circle in the center of the page. You then draw things around the circle. The things around the circle are called A closed system Something that sits next to the database An actor A Thing
Scenario 1: A privately owned company that sells industrial kitchen equipment needs to set-up a database for their employees. The company has vastly expanded over the last couple of years and needs to keep track of its employees’ salaries. The salaries are worked out according to the position of the person in the department that they work in. Each department has certain facilities, for example the director’s department has a smoking balcony, the factory has a water cooler e.t.c. Each position has the same salary, regardless of the department. For example, a manager in the factory has the same salary as the manager of the IT department. Each employee is uniquely identified by their employee’s ID number. The CEO would also like to know how many positions in each department there are, as well as the name of the branch each employee works for. Each branch must have the information of the country it is situated in. Question 1: Draw a complete entity relationship diagram with all…
database
Use the following instructions below to construct a crows foot erd please Suppose you are given the following requirements for a simple database for the National Football League (NFL): the NFL has many teams, each team has a name, a city, a coach, a captain, and a set of players, each player belongs to only one team, each player has a name, a position (such as left wing or goalie), a skill level, and a set of injury records, · a team captain is also a player, a game is played between two teams (referred to as Home Team and Guest team) and has a date (such as May 11th, 1999) and a score (such as 4 to 2) Instructions: Use the Case Study above to complete the following tasks 1. Identify the objects (entities) and attributes based on the information provided in the case study