"update" "non-redundant database" 2018 empirical citations 2000 questions

Questions

Designing and refining an Entity-Relationship Model A company wants a simple database to record information about...

Designing and refining an Entity-Relationship Model
A company wants a simple database to record information about ticket sale for
theatre performances. They describe the key elements of their requirements in the
following points:
• Customers have a name, phone number, a credit card no, and a unique customer
number.
• Customers can attend many performances, and each performance can have many
customers attending.
• Each performance of a show is on at a specific date and time, at a venue.
• Each performance has many actors and the actors in each performance can vary.
• Actors have a staff id, first name, last name, and a date of birth.
• A show has a title, year and duration in minutes. While two shows could have the same
title, no two shows in the same year have the same title.
• Shows can have many producers, each with a staff id, first name, last name, date of
birth.
Based on the information you gathered, model the activities in your client's business and present
your model as an Entity-Relationship (ER) diagram. Carefully state any assumptions that you
make. In your ER diagram, you must properly denote all applicable concepts, including weak or
strong entities, keys, composite or multi-valued attributes; relationships and their cardinality and
participation constraints.
After presenting your ER model to the management, they pointed out that your model lacks the
ability to capture all requirements of the operation. In particular, they pointed out the following
shortcomings:
1. Whenever customers want to attend a performance they must purchase a ticket, which
records the purchase date. They can use different credit cards for different purchases.
The customer account must be created prior to purchasing a ticket, and tickets are not
transferable.
2. Tickets are for a specific performance of a show and identify the seat number, and a
status (to indicate if the ticket has been redeemed).
3. There may be cases where performances of a show run concurrently.
4. Actors have a specific role that they play in each performance of the show which must be
recorded in the system.
5. Actors must have one understudy, who will perform their role in cases where the primary
actor is unavailable (eg due to illness). An understudy can study under many primary
actors.
6. Producers may have a single production company which has a unique name and has an
address. Each production company belongs to a single producer.
Draw a modified ER diagram to accommodate these additional requirements.
Note: Your answer to this question should include TWO complete ER diagrams

In: Computer Science

A database uses 20-character strings as record identifiers. The valid characters in these strings are upper-case...

A database uses 20-character strings as record identifiers. The valid characters in these strings are upper-case letters in the English alphabet and decimal digits. (Recall there are 26 letters in the English alphabet and 10 decimal digits.) How many valid record identifiers are possible if a valid record identifier must meet all of the following criteria:

Letter(s) from the set {A,E,I,O,U} occur in exactly three positions of the string.
The last three characters in the string are distinct decimal digits that do not appear elsewhere in the string.
The remaining characters of the string may be filled with any of the remaining letters or decimal digits.

In: Advanced Math

Using the U.S. Securities and Exchange Commission’s EDGAR database, select a public company that has consolidated...

Using the U.S. Securities and Exchange Commission’s EDGAR database, select a public company that has consolidated subsidiaries. To minimize the chances of classmates selecting the same company, select one that begins with the same letter as your first or last name. In your discussion post, name the company and all of its subsidiaries. Then answer the following questions: What is the accounting valuation basis for consolidating assets and liabilities in the business combination? What percentage ownership does the parent have in one of the subsidiaries reported? Are there any outside interests that have been accounted for with this subsidiary? Is there any goodwill recorded? If yes, are there notes in the financial statement regarding a goodwill impairment loss? If so, how were they recorded? If not, how and when should any goodwill impairment loss be recorded? My first name starts with an S.

Use the Sprint company

In: Accounting

Constructing a Yield Curve 1. Go to the St. Louis Federal Reserve FRED database, and find...

Constructing a Yield Curve 1. Go to the St. Louis Federal Reserve FRED database, and find daily yield data on the following U.S. treasuries securities: One-month (DGS1MO) Three-month (DGS3MO) Six-month (DGS6MO) One-year (DGS1) Two-year (DGS2) Three-year (DGS3) Five-year (DGS5) Seven-years (DGS7) Ten-year (DGS10) 20-year (DGS20) 30-year (DGS30) Download the last full year of data available into a spreadsheet.

a. Construct a yield curve by creating a line graph for the most recent day of data available, and for the same day (or as close to the same day as possible) one year prior, across all maturities. How do the yield curves compare? What does the changing slope say about potential changes in economic conditions?

b. Determine the date of the most recent Federal Open Market Committee (FOMC) policy statement. Construct yield curves for both the day before the policy statement was released and the day on which the policy was released. Was there any significant change in the yield curve as a result of the policy statement? How might this be explained?

In: Economics

Please write the SQL statement for the following. I am using the Adventurework2014 database. 1. Create...

Please write the SQL statement for the following. I am using the Adventurework2014 database.

1. Create a login for AdventureWorks employees. An employee login should be composed of the first letter of a person's first name combined with their last name. In addition, the login should be all lower case characters. All the required information is located in Person.Person table. Employees can be identified by "EM" value in the PersonType field. The output should include BusinessEntityID, first name (FirstName), last name (LastName), and EmpLogin alias for the login column. **Hint use the substring fuction which takes the following format:

- expression: is a character, binary, text, ntext, or image expression

-start is an integer of bigint expression that specifies where the returned characters start. (The numbering is 1 based, meaning that the first character in the expression is 1). if start is less than 1, the returned expression will begin at the first character that is specified in expression. In this case, the number of characters that are returned is the largest value of either the sum of start + length -1 or 0. If start is greater than the n umber of characters in the value expression, a zero length expression is returned.

-length - is a positive integer of bigint expression that specifies how many characters of the expression will be returned. If length is negative, an error is generated, and the statement is terminated. If the sum of start and length is greater than the number of characters in expression, the whole value expression beginning at start is returned.

2. From Sales.SalesOrderHeader table, list all orders (SalesOrderNumber, OrderDate) that were placed in June of 2011. Use date manipulation functions.

Please only answer if you know the correct answer. I will give a thumbs up if the answer is correct and down if incorrect.

In: Computer Science

Describe what each line does in the following SQL query The lyrics database is provided under...

Describe what each line does in the following SQL query

The lyrics database is provided under question 3 for context

1. select studioID, studioname, base from salespeople sa inner join studios st on (sa.salesID = st.salesid) where base < 300

2. select concat_ws(' ', firstname, lastname) as "Member Name"

from members;

3. select m.lastname, m.firstname, s.lastname

from members m inner join salespeople s using (salesID)

order by m.lastname asc;

The lyrics database is provided below

DROP TABLES IF EXISTS Artists,Genre, Members, Titles, Tracks,SalesPeople,Studios,XrefArtistsMembers;
DROP TABLES IF EXISTS Authors,Publishers,Titles,Title_Authors,Royalties;
DROP TABLES IF EXISTS Products,Customers,Orders,Order_details;
DROP TABLES IF EXISTS Sailors,Boats,Reserves;

CREATE TABLE Artists (
        ArtistID int, 
        ArtistName varchar (50) NOT NULL ,
        City varchar (25) NULL ,
        Region varchar (15) NULL ,
        Country varchar (20) NULL ,
        WebAddress varchar (40) NULL ,
        EntryDate date NULL ,
        LeadSource varchar (10) NULL 
);

Insert Into Artists Values(1,'The Neurotics','Peterson','NC','USA','www.theneurotics.com','2003-05-14','Directmail');
Insert Into Artists Values(2,'Louis Holiday','Clinton','IL','USA' ,NULL,'2003-06-03','Directmail');
Insert Into Artists Values(3,'Word','Anderson','IN','USA',NULL,'2003-06-08','Email');
Insert Into Artists Values(5,'Sonata','Alexandria','VA','USA','www.classical.com/sonata','2003-06-08','Ad');
Insert Into Artists Values(10,'The Bullets','Alverez','TX','USA',NULL,'2003-08-10','Email');
Insert Into Artists Values(14,'Jose MacArthur','Santa Rosa','CA','USA','www.josemacarthur.com','2003-08-17','Ad');
Insert Into Artists Values(15,'Confused','Tybee Island','GA','USA',Null,'2003-09-14','Directmail');
Insert Into Artists Values(17,'The Kicks','New Rochelle','NY','USA',NULL,'2003-12-03','Ad');
Insert Into Artists Values(16,'Today','London','ONT','Canada','www.today.com','2003-10-07','Email');
Insert Into Artists Values(18,'21 West Elm','Alamaba','VT','USA','www.21westelm.com','2003-02-05','Ad');
Insert Into Artists Values(11,'Highlander','Columbus','OH','USA',NULL,'2002-08-10','Email');

CREATE TABLE Genre (
        Genre varchar (15)  
);

Insert into Genre Values('alternative');
Insert into Genre Values('classical');
Insert into Genre Values('jazz');
Insert into Genre Values('metal');
Insert into Genre Values('R&B');
Insert into Genre Values('rap');
Insert into Genre Values('pop');

CREATE TABLE Members (
        MemberID int ,
        FirstName varchar (25) NULL ,
        LastName varchar (25) NULL ,
        Address varchar (60) NULL ,
        City varchar (25) NULL ,
        Region varchar (15) NULL ,
        PostalCode varchar (10) NULL ,
        Country varchar (20) NULL ,
        HomePhone varchar (16) NULL ,
        WorkPhone varchar (16) NULL ,
        EMail varchar (40) NULL ,
        Gender char (1) NULL ,
        Birthday date NULL ,
        SalesID smallint NULL 
);

Insert Into Members Values(10,'Roberto','Alvarez','Rt 1','Anderson','IN','46019','USA','7651552983','7651628837','[email protected]','M','1968-01-18',2);
Insert Into Members Values(31,'Jose','MacArthur','51444 Vine','Santa Rosa','CA','99999','USA','6331289393',Null,'[email protected]','M','1978-06-24',1);
Insert Into Members Values(13,'Mary','Chrisman','1772 East 117th','Fishers','IN','46123','USA','3171820387',Null,'[email protected]','F','1973-03-01',1);
Insert Into Members Values(15,'Warren','Boyer','167 Alamo Dr','Alverez','TX','75601','USA','8221722883',Null,'[email protected]','M','1969-04-19',2);
Insert Into Members Values(32,'Doug','Finney','2020 Dubois','Savannah','GA','30003','USA','9821222929',Null,'[email protected]','M','1963-08-04',3);
Insert Into Members Values(19,'Terry','Irving','18a 7th St','Tybee Island','GA','30004','USA','5411252093',Null,Null,'M','1959-06-22',3);
Insert Into Members Values(21,'Michelle','Henderson','201 Bonaventure','Savannah','GA','30005','USA','8221928273',Null,Null,'F','1964-03-15',2);
Insert Into Members Values(34,'William','Morrow','PO Box 1882','New Rochelle','NY','10014','USA','9981722928',Null,'[email protected]','M','1965-03-17',2);
Insert Into Members Values(29,'Frank','Payne','5412 Clinton','New Rochelle','NY','10014','USA','9981737464',Null,Null,'M','1960-01-17',1);
Insert Into Members Values(35,'Aiden','Franks','167 East 38th','Alverez','TX','75601','USA','8321729283','8321723833','[email protected]','M','1983-09-02',2);
Insert Into Members Values(3,'Bryce','Sanders','PO Box 1292','Peterson','NC','27104','USA','6441824283',Null,'[email protected]','M','1966-06-11',2);
Insert Into Members Values(14,'Carol','Wanner','787 Airport Rd','Alverez','TX','75601','USA','6831223944',Null,Null,'F','1978-11-08',3);
Insert Into Members Values(33,'Brian','Ranier','23 Gregory Lane','London','ONT','M6Y 2Y7 ','Canada','6231842933',Null,Null,'M','1957-10-19',3);
Insert Into Members Values(7,'Marcellin','Lambert','142 Sample Rd','Alexandria','VA','20102','USA','8331929302',Null,'[email protected]','M','1959-11-14',3);
Insert Into Members Values(8,'Caroline','Kale','1515 Stone Church Rd','Allen','VA','20321','USA','7321223742',Null,Null,'F','1956-05-30',3);
Insert Into Members Values(9,'Kerry','Fernandez','15 Midway','Lynchberg','VA','21223','USA','2211229384','2211223939',Null,'M','1962-01-16',1);
Insert Into Members Values(26,'Tony','Wong','115 Maple St','McKensie','ONT','M8H 3T1','Canada','3311692832','3311692822','[email protected]','M','1955-11-01',2);
Insert Into Members Values(18,'Bonnie','Taft','RR4','Alamaba','VT','05303','USA','3721223292',Null,'[email protected]','F','1960-09-21',1);
Insert Into Members Values(20,'Louis','Holiday','15 Davis Ct','Clinton','IL','63882','USA','1451223838',Null,Null,'M','1969-07-27',2);
Insert Into Members Values(22,'Bobby','Crum','RR2','Pine','VT','05412','USA','1831828211',Null,Null,'M','1965-06-10',3);
Insert Into Members Values(28,'Vic','Cleaver','100 Maple','Reston','VT','05544','USA','8111839292',Null,Null,'M','1957-02-10',2);
Insert Into Members Values(30,'Roberto','Goe','14 Gray Rd','Columbus','OH','48110','USA','2771123943',Null,Null,'M','1967-09-12',1);
Insert Into Members Values(36,'Davis','Goodman','2020 Country Rd','Columbus','OH','48318','USA','2771152882','2771128833','[email protected]','M','1980-10-27',2);


CREATE TABLE SalesPeople (
        SalesID smallint ,
        FirstName varchar (20) NOT NULL ,
        LastName varchar (20) NOT NULL ,
        Initials varchar (3) NULL ,
        Base decimal(5,2) NULL,
        Supervisor smallint NUll
);

Insert into SalesPeople Values(1,'Bob','Bentley','bbb',100,4);
Insert into SalesPeople Values(2,'Lisa','Williams','lmw',300,4);
Insert into SalesPeople Values(3,'Clint','Sanchez','cls',100,1);
Insert into SalesPeople Values(4,'Scott','Bull','sjb',Null, Null);      


CREATE TABLE Studios (
        StudioID int,
        StudioName varchar (40) NULL ,
        Address varchar (60) NULL ,
        City varchar (25) NULL ,
        Region varchar (15) NULL ,
        PostalCode varchar (10) NULL ,
        Country varchar (20) NULL ,
        WebAddress varchar (40) NULL ,
        Contact varchar (50) NULL ,
        EMail varchar (40) NULL ,
        Phone varchar (16) NULL ,
        SalesID smallint NULL 
);

Insert Into Studios Values(1,'MakeTrax','3000 S St Rd 9','Anderson','IN','46012','USA','www.maketrax.com','Gardner Roberts','[email protected]','7651223000',3);
Insert Into Studios Values(2,'Lone Star Recording','PO Box 221','Davis','TX','76382','USA','www.lsrecords.com','Manuel Austin','[email protected]','8821993748',2);
Insert Into Studios Values(3,'Pacific Rim','681 PCH','Santa Theresa','CA','99320','USA','www.pacrim.org','Harry Lee','[email protected]','3811110033',2);


CREATE TABLE Titles (
        TitleID int ,
        ArtistID int NULL ,
        Title varchar (50) NULL ,
        StudioID int NULL ,
        UPC varchar (13) NULL ,
        Genre varchar (15) NULL 
);

Insert Into Titles Values(1,1,'Meet the Neurotics',1,'2727366627','alternative');
Insert Into Titles Values(3,15,'Smell the Glove',2,'1283772282','metal');
Insert Into Titles Values(4,10,'Time Flies',3,'1882344222','alternative');
Insert Into Titles Values(5,1,'Neurotic Sequel',1,'2828830202','alternative');
Insert Into Titles Values(6,5,'Sonatas',2,'3999320021','classical');
Insert Into Titles Values(7,2,'Louis at the Keys',3,'3838227111','jazz');


CREATE TABLE Tracks (
        TitleID int NOT NULL ,
        TrackNum smallint NOT NULL ,
        TrackTitle varchar (50) NULL ,
        LengthSeconds smallint NULL ,
        MP3 smallint NULL ,
        RealAud smallint NULL 
);

Insert Into Tracks Values(1,1,'Hottie',233,1,1);
Insert Into Tracks Values(1,2,'Goodtime March',293,1,1);
Insert Into Tracks Values(1,3,'TV Day',305,1,1);
Insert Into Tracks Values(1,4,'Call Me an Idiot',315,1,1);
Insert Into Tracks Values(1,5,'25',402,1,1);
Insert Into Tracks Values(1,6,'Palm',322,1,1);
Insert Into Tracks Values(1,7,'Front Door',192,1,1);
Insert Into Tracks Values(1,8,'Where''s the Rain',175,1,1);
Insert Into Tracks Values(3,1,'Fat Cheeks',352,1,1);
Insert Into Tracks Values(3,2,'Rocky and Natasha',283,1,1);
Insert Into Tracks Values(3,3,'Dweeb',273,1,1);
Insert Into Tracks Values(3,4,'Funky Town',252,1,1);
Insert Into Tracks Values(3,5,'Shoes',182,1,1);
Insert Into Tracks Values(3,6,'Time In - In Time',129,1,1);
Insert Into Tracks Values(3,7,'Wooden Man',314,0,0);
Insert Into Tracks Values(3,8,'UPS',97,0,0);
Insert Into Tracks Values(3,9,'Empty',182,0,0);
Insert Into Tracks Values(3,10,'Burrito',65,0,0);
Insert Into Tracks Values(4,1,'Bob''s Dream',185,1,1);
Insert Into Tracks Values(4,2,'My Wizard',233,1,1);
Insert Into Tracks Values(4,3,'Third''s Folly',352,1,1);
Insert Into Tracks Values(4,4,'Leather',185,1,1);
Insert Into Tracks Values(4,5,'Hot Cars Cool Nights',192,1,1);
Insert Into Tracks Values(4,6,'Music in You',204,1,1);
Insert Into Tracks Values(4,7,'Don''t Care About Time',221,1,1);
Insert Into Tracks Values(4,8,'Kiss',218,1,1);
Insert Into Tracks Values(4,9,'Pizza Box',183,1,1);
Insert Into Tracks Values(4,10,'Goodbye',240,1,1);
Insert Into Tracks Values(5,1,'Song 1',285,1,1);
Insert Into Tracks Values(5,2,'Song 2',272,1,1);
Insert Into Tracks Values(5,3,'Song 3',299,1,1);
Insert Into Tracks Values(5,4,'Song 4',201,1,1);
Insert Into Tracks Values(5,5,'Song 5',198,1,0);
Insert Into Tracks Values(5,6,'Song 6',254,1,0);
Insert Into Tracks Values(5,7,'Song 7',303,1,1);
Insert Into Tracks Values(5,8,'Song 8',230,1,0);
Insert Into Tracks Values(5,9,'Song 8 and 1/2',45,1,0);
Insert Into Tracks Values(6,1,'Violin Sonata No. 1 in D Major',511,1,1);
Insert Into Tracks Values(6,2,'Violin Sonata No. 2 in A Major',438,1,1);
Insert Into Tracks Values(6,3,'Violin Sonata No. 4 in E Minor',821,1,0);
Insert Into Tracks Values(6,4,'Piano Sonata No. 1',493,1,0);
Insert Into Tracks Values(6,5,'Clarinet Sonata in E Flat',399,1,0);
Insert Into Tracks Values(7,1,'I Don''t Know',201,1,0);
Insert Into Tracks Values(7,2,'What''s the Day',332,1,0);
Insert Into Tracks Values(7,3,'Sirius',287,1,0);
Insert Into Tracks Values(7,4,'Hamburger Blues',292,1,0);
Insert Into Tracks Values(7,5,'Road Trip',314,1,0);
Insert Into Tracks Values(7,6,'Meeting You',321,1,1);
Insert Into Tracks Values(7,7,'Improv 34',441,1,1);
Insert Into Tracks Values(7,8,'Hey',288,1,1);


CREATE TABLE XrefArtistsMembers (
        MemberID int NOT NULL ,
        ArtistID int NOT NULL ,
        RespParty smallint NOT NULL 
       );

Insert into XrefArtistsMembers Values(20,2,1);
Insert into XrefArtistsMembers Values(31,14,1);
Insert into XrefArtistsMembers Values(3,1,1);
Insert into XrefArtistsMembers Values(10,3,1);
Insert into XrefArtistsMembers Values(13,3,0);
Insert into XrefArtistsMembers Values(7,5,1);
Insert into XrefArtistsMembers Values(8,5,0);
Insert into XrefArtistsMembers Values(9,5,0);
Insert into XrefArtistsMembers Values(32,15,0);
Insert into XrefArtistsMembers Values(19,15,1);
Insert into XrefArtistsMembers Values(21,15,0);
Insert into XrefArtistsMembers Values(34,17,1);
Insert into XrefArtistsMembers Values(29,17,0);
Insert into XrefArtistsMembers Values(15,10,1);
Insert into XrefArtistsMembers Values(35,10,0);
Insert into XrefArtistsMembers Values(14,10,0);
Insert into XrefArtistsMembers Values(33,16,1);
Insert into XrefArtistsMembers Values(26,16,0);
Insert into XrefArtistsMembers Values(18,18,1);
Insert into XrefArtistsMembers Values(28,18,0);
Insert into XrefArtistsMembers Values(22,18,0);
Insert into XrefArtistsMembers Values(30,11,1);
Insert into XrefArtistsMembers Values(36,11,0);

show tables;

In: Computer Science

Create a Database in POSTGRESQL using the following table names and attributes: users: userid (int, primary...

Create a Database in POSTGRESQL using the following table names and attributes:

users: userid (int, primary key), name (text)
movies: movieid (integer, primary key), title (text)
taginfo: tagid (int, primary key), content (text)
genres: genreid (integer, primary key), name (text)
ratings: userid (int, foreign key), movieid (int, foreign key), rating (numeric), timestamp (bigint, seconds since midnight Coordinated Universal Time (UTC) of January 1, 1970)
tags: userid (int, foreign key), movieid (int, foreign key), tagid (int, foreign key), timestamp (bigint, seconds since midnight Coordinated Universal Time (UTC) of January 1, 1970).
hasagenre: movieid (int, foreign key), genreid (int, foreign key)

In: Computer Science

Say you are a currency trader. You know from your Bloomberg database terminal that the spot...

Say you are a currency trader. You know from your Bloomberg database terminal that the spot rate is US$0.8545 = NZ$1, NZ Treasury Bills offer a return of 5 percent per anum, US Treasury Bills offer 2 percent per anum, and the one year forward rate is US$0.8224.

NewZealand dollar is domsetic currency.

a) Say you are trying to work out if the current forward rate to see if the markets are in equilibrium, or they are in disequilibrium and you can make profits by trading currencies. What would you conclude? Why?

b) We know the Reserve Bank is increasing domestic interest rates. How will this change your calculations? Why

In: Finance

the mysql lyrics database is provided below 1.)What is a primary key used for? Can you...

the mysql lyrics database is provided below

1.)What is a primary key used for? Can you give me an example of a primary key in the Lyrics database?

2.)What is the purpose of doing a join between two tables. Ex. Why would I ever want to join the Tracks and Titles tables together?

3.)Why is isolation important in database design?

DROP TABLES IF EXISTS Artists,Genre, Members, Titles, Tracks,SalesPeople,Studios,XrefArtistsMembers;
DROP TABLES IF EXISTS Authors,Publishers,Titles,Title_Authors,Royalties;
DROP TABLES IF EXISTS Products,Customers,Orders,Order_details;
DROP TABLES IF EXISTS Sailors,Boats,Reserves;

CREATE TABLE Artists (
   ArtistID int,
   ArtistName varchar (50) NOT NULL ,
   City varchar (25) NULL ,
   Region varchar (15) NULL ,
   Country varchar (20) NULL ,
   WebAddress varchar (40) NULL ,
   EntryDate date NULL ,
   LeadSource varchar (10) NULL
);

Insert Into Artists Values(1,'The Neurotics','Peterson','NC','USA','www.theneurotics.com','2003-05-14','Directmail');
Insert Into Artists Values(2,'Louis Holiday','Clinton','IL','USA' ,NULL,'2003-06-03','Directmail');
Insert Into Artists Values(3,'Word','Anderson','IN','USA',NULL,'2003-06-08','Email');
Insert Into Artists Values(5,'Sonata','Alexandria','VA','USA','www.classical.com/sonata','2003-06-08','Ad');
Insert Into Artists Values(10,'The Bullets','Alverez','TX','USA',NULL,'2003-08-10','Email');
Insert Into Artists Values(14,'Jose MacArthur','Santa Rosa','CA','USA','www.josemacarthur.com','2003-08-17','Ad');
Insert Into Artists Values(15,'Confused','Tybee Island','GA','USA',Null,'2003-09-14','Directmail');
Insert Into Artists Values(17,'The Kicks','New Rochelle','NY','USA',NULL,'2003-12-03','Ad');
Insert Into Artists Values(16,'Today','London','ONT','Canada','www.today.com','2003-10-07','Email');
Insert Into Artists Values(18,'21 West Elm','Alamaba','VT','USA','www.21westelm.com','2003-02-05','Ad');
Insert Into Artists Values(11,'Highlander','Columbus','OH','USA',NULL,'2002-08-10','Email');

CREATE TABLE Genre (
Genre varchar (15)
);

Insert into Genre Values('alternative');
Insert into Genre Values('classical');
Insert into Genre Values('jazz');
Insert into Genre Values('metal');
Insert into Genre Values('R&B');
Insert into Genre Values('rap');
Insert into Genre Values('pop');

CREATE TABLE Members (
   MemberID int ,
   FirstName varchar (25) NULL ,
   LastName varchar (25) NULL ,
   Address varchar (60) NULL ,
   City varchar (25) NULL ,
   Region varchar (15) NULL ,
   PostalCode varchar (10) NULL ,
   Country varchar (20) NULL ,
   HomePhone varchar (16) NULL ,
   WorkPhone varchar (16) NULL ,
   EMail varchar (40) NULL ,
   Gender char (1) NULL ,
   Birthday date NULL ,
   SalesID smallint NULL
);

Insert Into Members Values(10,'Roberto','Alvarez','Rt 1','Anderson','IN','46019','USA','7651552983','7651628837','[email protected]','M','1968-01-18',2);
Insert Into Members Values(31,'Jose','MacArthur','51444 Vine','Santa Rosa','CA','99999','USA','6331289393',Null,'[email protected]','M','1978-06-24',1);
Insert Into Members Values(13,'Mary','Chrisman','1772 East 117th','Fishers','IN','46123','USA','3171820387',Null,'[email protected]','F','1973-03-01',1);
Insert Into Members Values(15,'Warren','Boyer','167 Alamo Dr','Alverez','TX','75601','USA','8221722883',Null,'[email protected]','M','1969-04-19',2);
Insert Into Members Values(32,'Doug','Finney','2020 Dubois','Savannah','GA','30003','USA','9821222929',Null,'[email protected]','M','1963-08-04',3);
Insert Into Members Values(19,'Terry','Irving','18a 7th St','Tybee Island','GA','30004','USA','5411252093',Null,Null,'M','1959-06-22',3);
Insert Into Members Values(21,'Michelle','Henderson','201 Bonaventure','Savannah','GA','30005','USA','8221928273',Null,Null,'F','1964-03-15',2);
Insert Into Members Values(34,'William','Morrow','PO Box 1882','New Rochelle','NY','10014','USA','9981722928',Null,'[email protected]','M','1965-03-17',2);
Insert Into Members Values(29,'Frank','Payne','5412 Clinton','New Rochelle','NY','10014','USA','9981737464',Null,Null,'M','1960-01-17',1);
Insert Into Members Values(35,'Aiden','Franks','167 East 38th','Alverez','TX','75601','USA','8321729283','8321723833','[email protected]','M','1983-09-02',2);
Insert Into Members Values(3,'Bryce','Sanders','PO Box 1292','Peterson','NC','27104','USA','6441824283',Null,'[email protected]','M','1966-06-11',2);
Insert Into Members Values(14,'Carol','Wanner','787 Airport Rd','Alverez','TX','75601','USA','6831223944',Null,Null,'F','1978-11-08',3);
Insert Into Members Values(33,'Brian','Ranier','23 Gregory Lane','London','ONT','M6Y 2Y7 ','Canada','6231842933',Null,Null,'M','1957-10-19',3);
Insert Into Members Values(7,'Marcellin','Lambert','142 Sample Rd','Alexandria','VA','20102','USA','8331929302',Null,'[email protected]','M','1959-11-14',3);
Insert Into Members Values(8,'Caroline','Kale','1515 Stone Church Rd','Allen','VA','20321','USA','7321223742',Null,Null,'F','1956-05-30',3);
Insert Into Members Values(9,'Kerry','Fernandez','15 Midway','Lynchberg','VA','21223','USA','2211229384','2211223939',Null,'M','1962-01-16',1);
Insert Into Members Values(26,'Tony','Wong','115 Maple St','McKensie','ONT','M8H 3T1','Canada','3311692832','3311692822','[email protected]','M','1955-11-01',2);
Insert Into Members Values(18,'Bonnie','Taft','RR4','Alamaba','VT','05303','USA','3721223292',Null,'[email protected]','F','1960-09-21',1);
Insert Into Members Values(20,'Louis','Holiday','15 Davis Ct','Clinton','IL','63882','USA','1451223838',Null,Null,'M','1969-07-27',2);
Insert Into Members Values(22,'Bobby','Crum','RR2','Pine','VT','05412','USA','1831828211',Null,Null,'M','1965-06-10',3);
Insert Into Members Values(28,'Vic','Cleaver','100 Maple','Reston','VT','05544','USA','8111839292',Null,Null,'M','1957-02-10',2);
Insert Into Members Values(30,'Roberto','Goe','14 Gray Rd','Columbus','OH','48110','USA','2771123943',Null,Null,'M','1967-09-12',1);
Insert Into Members Values(36,'Davis','Goodman','2020 Country Rd','Columbus','OH','48318','USA','2771152882','2771128833','[email protected]','M','1980-10-27',2);

CREATE TABLE SalesPeople (
   SalesID smallint ,
   FirstName varchar (20) NOT NULL ,
   LastName varchar (20) NOT NULL ,
   Initials varchar (3) NULL ,
   Base decimal(5,2) NULL,
   Supervisor smallint NUll
);

Insert into SalesPeople Values(1,'Bob','Bentley','bbb',100,4);
Insert into SalesPeople Values(2,'Lisa','Williams','lmw',300,4);
Insert into SalesPeople Values(3,'Clint','Sanchez','cls',100,1);
Insert into SalesPeople Values(4,'Scott','Bull','sjb',Null, Null);

CREATE TABLE Studios (
   StudioID int,
   StudioName varchar (40) NULL ,
   Address varchar (60) NULL ,
   City varchar (25) NULL ,
   Region varchar (15) NULL ,
   PostalCode varchar (10) NULL ,
   Country varchar (20) NULL ,
   WebAddress varchar (40) NULL ,
   Contact varchar (50) NULL ,
   EMail varchar (40) NULL ,
   Phone varchar (16) NULL ,
   SalesID smallint NULL
);

Insert Into Studios Values(1,'MakeTrax','3000 S St Rd 9','Anderson','IN','46012','USA','www.maketrax.com','Gardner Roberts','[email protected]','7651223000',3);
Insert Into Studios Values(2,'Lone Star Recording','PO Box 221','Davis','TX','76382','USA','www.lsrecords.com','Manuel Austin','[email protected]','8821993748',2);
Insert Into Studios Values(3,'Pacific Rim','681 PCH','Santa Theresa','CA','99320','USA','www.pacrim.org','Harry Lee','[email protected]','3811110033',2);

CREATE TABLE Titles (
   TitleID int ,
   ArtistID int NULL ,
   Title varchar (50) NULL ,
   StudioID int NULL ,
   UPC varchar (13) NULL ,
   Genre varchar (15) NULL
);

Insert Into Titles Values(1,1,'Meet the Neurotics',1,'2727366627','alternative');
Insert Into Titles Values(3,15,'Smell the Glove',2,'1283772282','metal');
Insert Into Titles Values(4,10,'Time Flies',3,'1882344222','alternative');
Insert Into Titles Values(5,1,'Neurotic Sequel',1,'2828830202','alternative');
Insert Into Titles Values(6,5,'Sonatas',2,'3999320021','classical');
Insert Into Titles Values(7,2,'Louis at the Keys',3,'3838227111','jazz');

CREATE TABLE Tracks (
   TitleID int NOT NULL ,
   TrackNum smallint NOT NULL ,
   TrackTitle varchar (50) NULL ,
   LengthSeconds smallint NULL ,
   MP3 smallint NULL ,
   RealAud smallint NULL
);

Insert Into Tracks Values(1,1,'Hottie',233,1,1);
Insert Into Tracks Values(1,2,'Goodtime March',293,1,1);
Insert Into Tracks Values(1,3,'TV Day',305,1,1);
Insert Into Tracks Values(1,4,'Call Me an Idiot',315,1,1);
Insert Into Tracks Values(1,5,'25',402,1,1);
Insert Into Tracks Values(1,6,'Palm',322,1,1);
Insert Into Tracks Values(1,7,'Front Door',192,1,1);
Insert Into Tracks Values(1,8,'Where''s the Rain',175,1,1);
Insert Into Tracks Values(3,1,'Fat Cheeks',352,1,1);
Insert Into Tracks Values(3,2,'Rocky and Natasha',283,1,1);
Insert Into Tracks Values(3,3,'Dweeb',273,1,1);
Insert Into Tracks Values(3,4,'Funky Town',252,1,1);
Insert Into Tracks Values(3,5,'Shoes',182,1,1);
Insert Into Tracks Values(3,6,'Time In - In Time',129,1,1);
Insert Into Tracks Values(3,7,'Wooden Man',314,0,0);
Insert Into Tracks Values(3,8,'UPS',97,0,0);
Insert Into Tracks Values(3,9,'Empty',182,0,0);
Insert Into Tracks Values(3,10,'Burrito',65,0,0);
Insert Into Tracks Values(4,1,'Bob''s Dream',185,1,1);
Insert Into Tracks Values(4,2,'My Wizard',233,1,1);
Insert Into Tracks Values(4,3,'Third''s Folly',352,1,1);
Insert Into Tracks Values(4,4,'Leather',185,1,1);
Insert Into Tracks Values(4,5,'Hot Cars Cool Nights',192,1,1);
Insert Into Tracks Values(4,6,'Music in You',204,1,1);
Insert Into Tracks Values(4,7,'Don''t Care About Time',221,1,1);
Insert Into Tracks Values(4,8,'Kiss',218,1,1);
Insert Into Tracks Values(4,9,'Pizza Box',183,1,1);
Insert Into Tracks Values(4,10,'Goodbye',240,1,1);
Insert Into Tracks Values(5,1,'Song 1',285,1,1);
Insert Into Tracks Values(5,2,'Song 2',272,1,1);
Insert Into Tracks Values(5,3,'Song 3',299,1,1);
Insert Into Tracks Values(5,4,'Song 4',201,1,1);
Insert Into Tracks Values(5,5,'Song 5',198,1,0);
Insert Into Tracks Values(5,6,'Song 6',254,1,0);
Insert Into Tracks Values(5,7,'Song 7',303,1,1);
Insert Into Tracks Values(5,8,'Song 8',230,1,0);
Insert Into Tracks Values(5,9,'Song 8 and 1/2',45,1,0);
Insert Into Tracks Values(6,1,'Violin Sonata No. 1 in D Major',511,1,1);
Insert Into Tracks Values(6,2,'Violin Sonata No. 2 in A Major',438,1,1);
Insert Into Tracks Values(6,3,'Violin Sonata No. 4 in E Minor',821,1,0);
Insert Into Tracks Values(6,4,'Piano Sonata No. 1',493,1,0);
Insert Into Tracks Values(6,5,'Clarinet Sonata in E Flat',399,1,0);
Insert Into Tracks Values(7,1,'I Don''t Know',201,1,0);
Insert Into Tracks Values(7,2,'What''s the Day',332,1,0);
Insert Into Tracks Values(7,3,'Sirius',287,1,0);
Insert Into Tracks Values(7,4,'Hamburger Blues',292,1,0);
Insert Into Tracks Values(7,5,'Road Trip',314,1,0);
Insert Into Tracks Values(7,6,'Meeting You',321,1,1);
Insert Into Tracks Values(7,7,'Improv 34',441,1,1);
Insert Into Tracks Values(7,8,'Hey',288,1,1);

CREATE TABLE XrefArtistsMembers (
   MemberID int NOT NULL ,
   ArtistID int NOT NULL ,
   RespParty smallint NOT NULL
);

Insert into XrefArtistsMembers Values(20,2,1);
Insert into XrefArtistsMembers Values(31,14,1);
Insert into XrefArtistsMembers Values(3,1,1);
Insert into XrefArtistsMembers Values(10,3,1);
Insert into XrefArtistsMembers Values(13,3,0);
Insert into XrefArtistsMembers Values(7,5,1);
Insert into XrefArtistsMembers Values(8,5,0);
Insert into XrefArtistsMembers Values(9,5,0);
Insert into XrefArtistsMembers Values(32,15,0);
Insert into XrefArtistsMembers Values(19,15,1);
Insert into XrefArtistsMembers Values(21,15,0);
Insert into XrefArtistsMembers Values(34,17,1);
Insert into XrefArtistsMembers Values(29,17,0);
Insert into XrefArtistsMembers Values(15,10,1);
Insert into XrefArtistsMembers Values(35,10,0);
Insert into XrefArtistsMembers Values(14,10,0);
Insert into XrefArtistsMembers Values(33,16,1);
Insert into XrefArtistsMembers Values(26,16,0);
Insert into XrefArtistsMembers Values(18,18,1);
Insert into XrefArtistsMembers Values(28,18,0);
Insert into XrefArtistsMembers Values(22,18,0);
Insert into XrefArtistsMembers Values(30,11,1);
Insert into XrefArtistsMembers Values(36,11,0);

show tables;

In: Computer Science

using the lyrics database schema in mysql provided below. 1.)List the artist name of the artists...

using the lyrics database schema in mysql provided below.

1.)List the artist name of the artists who do not have a webaddress and their leadsource is “Directmail”?

2.)List the names of members in the artist called 'Today'.

3.)Report the total runtime in minutes FOR EACH album in the Titles table.

4.)List the firstname, lastname of members who are represented by the salesperson “Lisa Williams”

5.)List EACH salesperson’s firstname along with the number of Members that EACH SalesPerson represents.

below is the lyric schema used on this assigment for mysql. copy paste it in the mysql console