A Guide to SQL
9th Edition
ISBN: 9781111527273
Author: Philip J. Pratt
Publisher: Course Technology Ptr
expand_more
expand_more
format_list_bulleted
Question
Chapter 2, Problem 3CAT
Program Plan Intro
Shorthand representation of the relational
- The shorthand representation is used to represent the database table in an easier and an understandable way by using primary keys.
- The step wise process for representing a shorthand representation of a database structure is as follows:
- First, the Table Name is written down.
- Using paranthesis, list down all the columns or fields in the table within the paranthesis.
- From the shorthand representation, data such as primary keys can be identified.
- In this kind of representation, bold or underline data is used to represent a Primary key.
Entity Relationship Diagram (ERD):
Entity relationship diagram is basically a picture or snapshot about the business system. It means that the information stored and created is represented by this diagram.
- Entities may represent the physical object, event, or concept.
- An attribute is a property of an entity.
- Relationship denotes the link between the entities.
- If an independent child entity is present in a relationship then it is called a non-identifying relationship.
- If a dependent child entity is present in a relationship then it is called a non-identifying relationship.
Expert Solution & Answer
Trending nowThis is a popular solution!
Students have asked these similar questions
Database Foundations:
Design a database to produce the following reports. Do not use any surrogate keys in your design.
For each guide, list the guide number, guide last name, guide first name, address, city, state, postal code, telephone number, and date hired.
For each trip, list the trip ID number, the trip name, the location from which the trip starts, the state in which the trip originates, the trip distance, the maximum group size, the type of trip (hiking, biking, or paddling), the season in which the trip occurs, and the guide number, first name, and last name of each guide. A guide may lead many trips and a trip may be led by many different guides.
For each client, list the client number, client last name, client first name, address, city, state, postal code, and telephone number
The 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 MAINTENACNE_TYPE_ID should be foreign keys to their original tables. Cascade update and cascade delete the foreign keys.
The database should contain the following six tables:
Artists (artist_id: char(30), artist_name: char(30), artist_pop: int(5))Tracks (track_id: char(30), track_name: char(30), duration: int(10), tempo:real) Record (artist_id: char(30), track_id: char(30))Users (user_id: char(30), user_name: char(30), age: int(5), nationality: char(30), num_track_listened: int(10))Listen (user_id: char(30), track_id: char(30))Follow (user_id: char(30), artist_id: char(30), follow_date: datetime)
Write a single SQL query for each request below.
Find the ids of those users who have listened to at least one track but have not followed any artists.
Find the ids and names of those users who have listened to some tracks recorded by the artist named 'Adele'.
Find the ids and names of those users who have not listened to any track recorded by the artist named 'Adele'. Use ''NOT EXISTS'' to answer this query.
Chapter 2 Solutions
A Guide to SQL
Ch. 2 - Prob. 1RQCh. 2 - What is an attribute?Ch. 2 - What is a relationship? What is a one-to-many...Ch. 2 - Prob. 4RQCh. 2 - What is a relation?Ch. 2 - Prob. 6RQCh. 2 - Prob. 7RQCh. 2 - How do you qualify the name of a field, and when...Ch. 2 - Prob. 9RQCh. 2 - What is a primary key? What is the primary key for...
Ch. 2 - Prob. 11RQCh. 2 - Prob. 12RQCh. 2 - Define second normal form. What types of problems...Ch. 2 - Define third normal form. What types of problems...Ch. 2 - Prob. 15RQCh. 2 - Prob. 16RQCh. 2 - List the changes you would need to make to your...Ch. 2 - Prob. 1TDCh. 2 - Prob. 2TDCh. 2 - Prob. 3TDCh. 2 - Prob. 4TDCh. 2 - Prob. 5TDCh. 2 - Prob. 1CATCh. 2 - Identify the functional dependencies in the...Ch. 2 - Prob. 3CATCh. 2 - Determine the functional dependencies that exist...Ch. 2 - Prob. 2SCGCh. 2 - Prob. 3SCG
Knowledge Booster
Similar questions
- An EMPLOYEES table was added to the JustLee Books database to track employee information. Display a list of each employee’s name, job title, and manager’s name. Use column aliases to clearly identify employee and manager name values. Include all employees in the list and sort by manager name.arrow_forwardQ2 Create a Database with at least 4 tables, 3 queries, 2 forms, and 1 report. Each table should have at least 10 records. for library designarrow_forwardUsing the Henry Books database,update the paperback field for the book titled Black Houseto Y. You will insert 2 snips for this question. Insert a snip of the construct used to update the table: Insert your screen shot of the Books table here (be certain Black Housedisplays): Using the Henry Books database,add a new row to the AUTHORS table. The new author’s name is John Grisham. You will insert 2 snip s for this question. Insert a snip of the construct used to update the table: Insert your snip of the Authors table here showing the update: Using the Henry Books database,create a view named Paperback. It consists of the book code, book title, publisher name, and book price for every book that is available in paperback. You will insert 2 snips for this question. Insert a snip of the construct used to create this view: Run a query using this view and insert a snip of the resultset: Using the Henry Books database,write a stored procedure that…arrow_forward
- Using the university schema, write an SQL query to find the name and ID ofeach History student whose name begins with the letter ‘D’ and who has nottaken at least five Music coursesarrow_forwardPersonal Insurance, Inc. is a national company that insures homeowners and renters. It also offers umbrella policies that provide additional coverage. As a regional manager, you need to be able to query the database to help make decisions and to answer questions from other employees. Create a query based on the Customers table in Query Design View with the following options: Add the CustomerID, FirstName, LastName, and DateOfBirth fields to the design grid in that order. Sort the records in ascending order by LastName. Save the query using CustomerLastNameSorted as the name.Open the query in Datasheet View, then close it, saving if necessary. Open the HomeownersPremium query in Design View and make the following changes to the query: Delete the CustomerID column from the design grid. Add criteria to select only those records where the Premium field value is greater than 2,000. Save the changes to the HomeownersPremiumOpen the query in Datasheet View, then close it, saving if…arrow_forwardWhat data types would you use for the attributes in this entity? How would you code a CREATE TABLE statement to implement this entity as a table in the database?arrow_forward
- Implement a new independent entity phone in the Sakila database. Attributes and relationships are shown in the diagram below The diagram uses Sakila naming conventions. Follow the Sakila conventions for your table and column names: All lower case Underscore separator between root and suffix Foreign keys have the same name as referenced primary key Write CREATE TABLE and ALTER TABLE statements that: Implement the entity as a new phone table. Implement the has relationships as foreign keys in the Sakila customer, staff, and store tables. Remove the existing phone column from the Sakila address table. Step 2 requires adding a foreign key constraint to an existing table. Ex: ALTER TABLE customer ADD FOREIGN KEY (phone_id) REFERENCES phone(phone_id) ON DELETE SET NULL ON UPDATE CASCADE; Specify data types as follows: phone_id, phone_number, and country_code have data type INT. phone_type has date type VARCHAR(12) and contains strings like 'Home', 'Mobile', and 'Other'. Apply these…arrow_forwardIN a staywell Database Model, Task 1: For every property, list the management office number, address, monthly rent, owner number, owner’s first name, and owner’s last name. table should have this headings ; OFFICE_NUM ADDRESS MONTHLY_RENT OWNER_NUM FIRST_NAME LAST_NAMEarrow_forwardStayWell also rents out properties on a weekly basis to students attending summer school in the Seattle area. Design a database to meet the following requirements, using the shorthand representation and a diagram of your choice. For each student renter, list his or her number, first name, middle initial, last name, address, city, state, postal code, telephone number, and e-mail address. For each property, list the office number, property address, city, state, postal code, square footage, number of bedrooms, number of floors, maximum number of persons that can sleep in the unit, and the base weekly rate. For each rental agreement, list the renter number, first name, middle initial, last name, address, city, state, postal code, telephone number, start date of the rental, end date of the rental, and the weekly rental amount. The rental period is one or more weeks.arrow_forward
- StayWell also rents out properties on a weekly basis to students attending summer school in the Seattle area. Design a database to meet the following requirements, using the shorthand representation and a diagram of your choice. For each student renter, list his or her number, first name, middle initial, last name, address, city, state, postal code, telephone number, and e-mail address. For each property, list the office number, property address, city, state, postal code, square footage, number of bedrooms, number of floors, maximum number of persons that can sleep in the unit, and the base weekly rate. For each rental agreement, list the renter number, first name, middle initial, last name, address, city, state, postal code, telephone number, start date of the rental, end date of the rental, and the weekly rental amount. The rental period is one or more weeks.arrow_forwardStayWell also rents out properties on a weekly basis to students attending summer school in the Seattle area. Design a database to meet the following requirements, using the shorthand representation and a diagram of your choice. For each student renter, list his or her number, first name, middle initial, last name, address, city, state, postal code, telephone number, and e-mail address. For each property, list the office number, property address, city, state, postal code, square footage, number of bedrooms, number of floors, maximum number of persons that can sleep in the unit, and the base weekly rate. For each rental agreement, list the renter number, first name, middle initial, last name, address, city, state, postal code, telephone number, start date of the rental, end date of the rental, and the weekly rental amount. The rental period is one or more weeks.arrow_forwardCreate the following table in your database with the following schema: Table: Customer | Column Name Туре | id | int | name varchar | | int | referee id id is the primary key column for this table. Each row of this table indicates the id of a customer, their name, and the id of the customer who referred them. Add the following data to your tables: Input: Customer table: +-- | id | name | referee id | | 1 | 2 | 3 | 4 | 5 | 6 | will | null | Jane | null | Alex | 2 | Bill | null | Zack | 1 | Mark | 2 +--- Write an SQL query to report the IDs of the customer that are not referred by the customer with id = 2. Return the result table in any order. The results should be: Output: | name | | will | | Jane | | Bill | | Zack |arrow_forward
arrow_back_ios
SEE MORE QUESTIONS
arrow_forward_ios
Recommended textbooks for you
A Guide to SQLComputer ScienceISBN:9781111527273Author:Philip J. PrattPublisher:Course Technology Ptr
A Guide to SQL
Computer Science
ISBN:9781111527273
Author:Philip J. Pratt
Publisher:Course Technology Ptr