Suppose you are given the following requirements for a simple database for the Canadian football league: The league has many teams. • Each team has a unique name, a city, a coach, a captain and a set of players. Each player belongs to only one team with a unique number within the team. Different teams have players with the same numbers. • A team captain is also a player. • Each player has a number, name, a position, a skill level and a set of injury records. An injury record has a unique ID and a description. ● ● • A game is played between two teams (referred to as host_team and guest_team) and has a date and a score. Construct an E/R diagram for the database above. Clearly indicate the primary key(s) by underlining them as well as cardinality mappings in your E/R diagram.

Suppose you are given the following requirements for a simple database for the Canadian football league: The league has many teams. • Each team has a unique name, a city, a coach, a captain and a set of players. Each player belongs to only one team with a unique number within the team. Different teams have players with the same numbers. • A team captain is also a player. • Each player has a number, name, a position, a skill level and a set of injury records. An injury record has a unique ID and a description. ● ● • A game is played between two teams (referred to as host_team and guest_team) and has a date and a score. Construct an E/R diagram for the database above. Clearly indicate the primary key(s) by underlining them as well as cardinality mappings in your E/R diagram.

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

Consider a PUBLICATION COMPANY database in which data requirements are summarized as follows: Every topic should contain name, code and research area. The topics can have several reports and the reports may contain several topics in them. The report should have a title. A department can publish more than one report. A report can be published only by a single department. The department must have its name and address. Reports can be written by many contractors and contractors can write many reports. Each contractor should have name and address. A publication can include several topics and a topic can be present in more than one publications. A publication must have its title and code. One department can have many publications and a publication can be made only by one department. Construct an E-R diagram (based on a Chen's model) to represent the above requirements. Make sure you include all appropriate entities, relationships, attributes, and cardinalities.
Your job as a database designer is to design a database with a schema that captures all the information that galleries need to maintain. Galleries keep information about artists, their names (which are unique), birthplaces, age, and style of art. For each piece of artwork, the artist, the year it was made, its unique title, its type of art (e.g., painting, photograph, sculpture), and its price must be stored. Pieces of artwork also classified into groups of various kinds, for example, portraits, still lives, works by Picasso, or works of the 19th century; a given piece may belong to more than one group. Each group is identified by a name (like those just given) that describes the group and property. Finally, galleries keep information about customers. For each customer, galleries keep that person’s unique ID, name, address, total amount of dollars spent in the gallery, and the artists and groups of art that the customer tends to like. Draw the ER diagram for the database.
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 homeTeamID, 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…
Q3. Suppose you are given the following business rules to form the basis for a database design. The database must enable the manager of a company dinner club to mail invitations to the club's members, to plan the meals, to keep track of who attends the dinners, and so on. Each dinner serves many members, and each member may attend many dinners. A member receives many invitations, and each invitation is mailed to many members. A dinner is based on a single entree, but an entree may be used as the basis for many dinners. For example, a dinner may be composed of a fish entree, rice, and corn. Or the dinner may be composed of a fish entree, a baked potato, and string beans. Because the manager is not a database expert, the first attempt at creating the database uses the structure shown in the table below: Attribute Name MEMBER_NUM MEMBER_NAME MEMBER_ADDRESS MEMBER_CITY MEMBER_ZIPCODE INVITE_NUM INVITE_DATE ACCEPT_DATE DINNER_DATE DINNER_ATTENDED DINNER_CODE DINNER_DESCRIPTION Sample Value…
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…
Question A city college would like to maintain their academic information in a MySql database system. The following are the interested information: Courses: Catalog # (M170 for instance) Credit Hours; Name of the Course (Database Design Fundamentals for instance) Course Descriptions Prerequisite Components (Lecture, or Laboratory for instance) Classes: Class # Class Name (usually the course name, M 170 for instance) Start Date End Date Meeting time (9:30 am ~ 12:15 pm, for instance) Days in a week (Monday) Term (Spring 2018, for instance) Location (TIE Building for instance) Room (UB 301, for instance) Instructor Online status (online, or in-person) Faculty: Employee Id (001880301, for instance) First Name Last Name Title (professor, for instance) Email Phone Office Location (TIE building, for instance) Room (UB303, for instance) Student: Student ID First Name Last Name Plan Sub Plan Advisor Current GPA Grade of each course The Term you got the grade for a course. The class…
Produce the 3NF for the following: Scenario 1 This is a simple list table of a company trying to keep track of parts that they sell and orders that came in purchasing those parts (in other words, not a database but a flat one table file) You will design a database for this company so that they won't be relying on a simple 1 table list system to keep track of their data. Looking at the table below, produce the IN of the data OrderDate 10/20/2010 10/20/2010 10/20/2010 10/21/2010 10/21/2010 10/23/2010 10/21/2010 10/23/2010 10/23/2010 OrderNum 21608 PartNum AT94 DR93 NumOrdered 11 1 Description QuotedPrice $21.95 $495.00 $399.99 $329.95 S595 00 $794.95 $150.00 $495.00 00 0621S Iron 21610 Gas Range 21610 21613 DW11 Washer KL62 Dryer Dishwasher Home Gym Microwave Oven Gas Range Treadmill 21614 21617 ктоз 21617 CDS2 4. 21619 DR93 1. 21523 KV29
A taxi company would like to provide an online service for customers to book a trip and for taxi drivers to check their upcoming trips. A database needs to be created to store customer data (name, address, email, telephone number) and trip information (pick up and drop off address and pick up time and date). Furthermore, your database should contain driver information (name and license plate). Each trip must be assigned to exactly one driver (and trips for a driver must not overlap). Customers who are not registered yet need to register (providing their address, phone number, email address, and credit card information). A registered customer should be able to book a trip, i.e. provide the pick-up address and time as well as the drop-off address via a graphical user interface. Customers should also be able to view their trips and cancel them if needed. All these operations are only possible after a customer signs in (which means they must be registered). Each new trip must be confirmed…
Suppose you are given the following business rules to form the basis for a database design. The database must enable the manager of a company dinner club to mail invitations to the club's members, to plan the meals, to keep track of who attends the dinners, and so on. Each dinner serves many members, and each member may attend many dinners. A member receives many invitations, and each invitation is mailed to many members. A dinner is based on a single entree, but an entree may be used as the basis for many dinners. For example, a dinner may be composed of a fish entree, rice, and corn, or the dinner may be composed of a fish entree, a baked potato, and string beans. Because the manager is not a database expert, the first attempt at creating the database uses the structure shown in Table P6.7.a. Given the table structure illustrated in Table P6.7, write the relational schema and draw its dependency diagram. Label all transitive and/or partial dependencies. (Hint: This structure uses a…
Create a Crow’s Foot notation ERD to support the following business operations: A friend of yours has opened Professional Electronics and Repairs (PEAR) to repair smartphones, laptops, tablets, and MP3 players. She wants you to create a database to help her run her business.   When a customer brings a device to PEAR for repair, data must be recorded about the customer, the device, and the repair. The customer’s name, address, and a contact phone number must be recorded (if the customer has used the shop before, the information already in the system for the customer is verified as being current). For the device to be repaired, the type of device, model, and serial number are recorded (or verified if the device is already in the system). Only customers who have brought devices into PEAR for repair will be included in this system.   Since a customer might sell an older device to someone else who then brings the device to PEAR for repair, it is possible for a device to be brought in for…
Answer the following prompts 1The InstantRide Management team founded a new team for car maintenance. The new team is responsible for the small maintenance operations for the cars in the InstantRide system. The main idea is to take actions faster and minimize the time spent for the maintenance. Therefore, the Car Maintenance team wants to store MAINTENANCE_TYPE_ID (char(5)) and a MAINTENANCE_TYPE_DESCRIPTION (varchar(30)) in the database. Using MAINTENANCE_TYPE_ID as the PRIMARY KEY, create a new table, MAINTENANCE_TYPES, and send the table description with the column names and types to the Car Maintenance team. 2The Car Maintenance team also wants to store the actual maintenance operations in the database. The team wants to start with a table to store CAR_ID (CHAR(5)), MAINTENANCE_TYPE_ID (CHAR(5)) and MAINTENANCE_DUE (DATE) date for the operation. Create a new table named MAINTENANCES. The PRIMARY_KEY should be the combination of the three fields. The CAR_ID and MAINTENANCE_TYPE_ID…
Consider a database with the following three tables: RACES[RaceName, Year, Organizer, Country, Federation, Type, Distance] RaceName and Year form the primary key. Type is from { Run, Triathlon, Bike, MountainBike }. Distance is given in meters. RESULTS[RaceName, Year, Place, AthleteName] (RaceName, Year, Place) form the primary key. (RaceName, Year) form a foreign key referencing RACES. RECORDS[RaceName, Year, Record, Time] (RaceName, Year, Record) form the primary key. The foreign key is the same as for RESULTS. The values of Record could be, e.g., World Record, National Record, Fastest Time. Time is in seconds. Write queries in SQL that produce the answers to the following questions: (a) Find the names of all athletes who exclusively participate in runs that are longer than 40 kilometers. (b) Find organizers who organized a mountain-bike and a bike race, where the mountain-bike race was longer than the bike race. (c) Find the names of all athletes who achieved a world record in…