1)Given a list L1, create a list L2 that contains all but the last element of L1 (e.g. if L1 is ["a","z",True], L2 should be equal to ["a","z"]

2)Given a string s, assign a variable has_z that is True if s contains the letter "z" anywhere within it.

3)Write Python code that calculates the mean of a given list of numbers x and saves the result in a variable m.

4)Given two numeric variables, x and y, assign the maximum of x and y to a variable m.

5)given a string s, write Python code that sets a boolean variable has_two_caps to True if the string contains exactly two capital letters (use the .isupper() string method to test individual letters) and False otherwise.

6)Given two variables x and y containing numerical values and a boolean variable use_x, assign a third variable z the value of x if use_x is True; assign it the value of y if use_x is False.

7)Given a list of logical values x, set all_x to True if all of the values in x are True, and False otherwise

IN PYTHON!!

Task 1.

For each table on the list, identify the functional dependencies. List the functional dependencies. Normalize the relations to BCNF. Then decide whether the resulting tables should be implemented in that form. If not, explain why. For each table, write the table name and write out the names, data types, and sizes of all the data items, Identify any constraints, using the conventions of the DBMS you will use for implementation. Write and execute SQL statements to create all the tables needed to implement the design.

Create indexes for foreign keys and any other columns that will be used most often for queries. Insert about five records in each table, preserving all constraints. Put in enough data to demonstrate how the database will function. Write SQL statements that will process five non-routine requests for information from the database just created. For each, write the request in English, followed by the corresponding SQL command. Create at least one trigger and write the code for it.

Tables / DDL and Insert Data have been provided below:

-- DDL to create the MS SQL tables for initial relational model for Theater Group
CREATE DATABASE Theater;

CREATE TABLE Member(
    memId       INT,
    dateJoined   DATETIME,
    firstname    VARCHAR(15),
    lastName    VARCHAR(20),
    street       VARCHAR(50),
    city        VARCHAR(15),
    state        CHAR(2),
    zip           CHAR(5),
    areaCode    CHAR(3),
    phoneNumber CHAR(7),
    currentOfficeHeld VARCHAR(20),
    CONSTRAINT Member_memId_pk PRIMARY KEY(memid));

CREATE TABLE Sponsor(
    sponID       INT,
    name       VARCHAR(20),
    street       VARCHAR(50),
    city        VARCHAR(15),
    state        CHAR(2),
    zip           CHAR(5),
    areaCode    CHAR(3),
    phoneNumber CHAR(7),
    CONSTRAINT Sponsor_sponId_pk PRIMARY KEY(sponID));

CREATE TABLE Subscriber(
    subID       INT,
    firstname    VARCHAR(15),
    lastName    VARCHAR(20),
    street       VARCHAR(50),
    city        VARCHAR(15),
    state        CHAR(2),
    zip           CHAR(5),
    areaCode    CHAR(3),
    phoneNumber   CHAR(7),
    CONSTRAINT Subscriber_subId_pk PRIMARY KEY(subID));

CREATE TABLE Play(
    title            VARCHAR(100),
    author            VARCHAR(35),
    numberOfActs   SMALLINT,
    setChanges        SMALLINT,
    CONSTRAINT Play_title_pk PRIMARY KEY(title));

CREATE TABLE Production(
    year            SMALLINT,
    seasonStartDate   VARCHAR(7),
    seasonEndDate   VARCHAR(7),
    title            VARCHAR(100),
    CONSTRAINT Prod_year_seasStDate_pk primary key(year, seasonStartDate),
    CONSTRAINT Prod_title_fk FOREIGN KEY(title) REFERENCES Play(title));

CREATE TABLE Performance(
    datePerf        VARCHAR(7),
    timePerf        VARCHAR(10),
    year            SMALLINT,
    seasonStartDate   VARCHAR(7),
    CONSTRAINT Performance_date_pk PRIMARY KEY(datePerf,year),
    CONSTRAINT Performance_yr_seasStart_fk FOREIGN KEY(year,seasonStartDate) REFERENCES Production(year, seasonStartDate));

CREATE TABLE TicketSale(
    saleID       INT,
    saleDate   DATETIME,
    totalAmount   DECIMAL(6,2),
    perfDate   VARCHAR(7),
    perfYear   SMALLINT,
    subId       INT,
    CONSTRAINT TicketSale_ID_PK PRIMARY KEY(saleId),
    CONSTRAINT TicketSale_perfDate_fk FOREIGN KEY(perfDate,perfYear) REFERENCES Performance(datePerf,year),
    CONSTRAINT TicketSale_subId_fk FOREIGN KEY(subId) REFERENCES Subscriber(subId));

CREATE TABLE DuesPayment(
    memId        INT,
    duesYear   SMALLINT,
    amount       DECIMAL(5,2),
    datePaid   DATETIME,
    CONSTRAINT DuesPayment_memId_year_pk PRIMARY KEY(memid, duesyear),
    CONSTRAINT DuesPayment_memId_fk FOREIGN KEY(memid) REFERENCES Member(memid));

CREATE TABLE Donation(
    sponId            INT,
    donationDate   DATETIME,
    donationType   VARCHAR(20),
    donationValue   DECIMAL(8,2),
    year            SMALLINT,
    seasonStartDate   VARCHAR(7),
    CONSTRAINT Donation_sponId_date_pk PRIMARY KEY(sponId, donationDate),
    CONSTRAINT Donation_sponId_fk FOREIGN KEY(sponId) REFERENCES Sponsor(sponId),
    CONSTRAINT Donation_year_seasStartDate_fk FOREIGN KEY(year,seasonStartDate) REFERENCES Production(year, seasonStartDate));

CREATE TABLE Ticket(
    saleId            INT,
    seatLocation   VARCHAR(3),
    price            DECIMAL(5,2),
    seattype        VARCHAR(15),
    CONSTRAINT Ticket_saleid_pk PRIMARY KEY(saleId, seatLocation),
    CONSTRAINT Ticket_saleid_fk FOREIGN KEY(saleid) REFERENCES TicketSale(saleId));

CREATE TABLE Member_Production(
    memId            INT,
    year            SMALLINT,
    seasonStartDate   VARCHAR(7),
    role            VARCHAR(25),
    task            VARCHAR(25),
    CONSTRAINT Mem_Prod_Id_year_seas_pk PRIMARY KEY(memId, year, seasonStartDate),
    CONSTRAINT Mem_Prod_memId_FK FOREIGN KEY (memid) REFERENCES Member(memId),
    CONSTRAINT Mem_Prod_yr_seasStartDate_fk FOREIGN KEY(year,seasonStartDate) REFERENCES Production(year,seasonStartDate));

INSERT DATA:

-- insert some records

INSERT INTO Member values(11111,'01-Feb-2015', 'Frances','Hughes','10 Hudson Avenue','New Rochelle','NY','10801','914','3216789','President');
INSERT INTO Member values(22222,'01-Mar-2015', 'Irene','Jacobs','1 Windswept Place','New York','NY','10101','212','3216789','Vice-President');
INSERT INTO Member values(33333,'01-May-2015', 'Winston', 'Lee','22 Amazon Street','New York','NY', '10101','212','3336789',null);
INSERT INTO Member values(44444,'01-Feb-2015', 'Ryan','Hughes','10 Hudson Avenue','New Rochelle','NY','10801','914','5556789','Secretary');
INSERT INTO Member values(55555,'01-Feb-2015', 'Samantha', 'Babson','22 Hudson Avenue','New Rochelle','NY','10801','914','6666789','Treasurer');
INSERT INTO Member values(66666,'01-Feb-2015', 'Robert', 'Babson','22 Hudson Avenue','New Rochelle','NY','10801','914','6666789',null);

INSERT INTO Sponsor values(1234, 'Zap Electrics', '125 Main Street','New York','NY', '10101', '212','3334444');
INSERT INTO Sponsor values(1235, 'Elegant Interiors', '333 Main Street','New York','NY', '10101', '212','3334446');
INSERT INTO Sponsor values(1236, 'Deli Delights', '111 South Street', 'New Rochelle','NY','10801', '914','2224446');

INSERT INTO Subscriber values(123456, 'John','Smith','10 Sapphire Row', 'New Rochelle','NY','10801', '914','1234567');
INSERT INTO Subscriber values(987654, 'Terrence','DeSimone','10 Emerald Lane','New York','NY', '10101','914','7676767');

INSERT INTO Play values('Macbeth','Wm. Shakespeare', 3,6);
INSERT INTO Play values('Our Town','T. Wilder', 3,4);
INSERT INTO Play values('Death of a Salesman','A. Miller', 3,5);

INSERT INTO Production values(2015,'05-May', '14-May', 'Our Town');
INSERT INTO Production values(2014,'14-Oct','23-Oct','Macbeth');

INSERT INTO Performance values('05-May','8pm',2015,'05-May');
INSERT INTO Performance values('06-May','8pm',2015,'05-May');
INSERT INTO Performance values('07-May','3pm',2015,'05-May');
INSERT INTO Performance values('12-May','8pm',2015,'05-May');
INSERT INTO Performance values('13-May','8pm',2015,'05-May');
INSERT INTO Performance values('14-May','3pm',2015,'05-May');
INSERT INTO Performance values('14-Oct','8pm',2014,'14-Oct');
INSERT INTO Performance values('15-Oct','8pm',2014,'14-Oct');
INSERT INTO Performance values('16-Oct','3pm',2014,'14-Oct');
INSERT INTO Performance values('21-Oct','8pm',2014,'14-Oct');
INSERT INTO Performance values('22-Oct','8pm',2014,'14-Oct');
INSERT INTO Performance values('23-Oct','3pm',2014,'14-Oct');

INSERT INTO TicketSale values(123456,'01-May-2015',40.00,'05-May',2015,123456);
INSERT INTO Ticket values(123456, 'A1',20.00,'orch front');
INSERT INTO Ticket values(123456, 'A2',20.00,'orch front');

INSERT INTO TicketSale values(123457,'02-May-2015',80.00,'05-May',2015,987654);
INSERT INTO Ticket values(123457, 'A3',20.00,'orch front');
INSERT INTO Ticket values(123457, 'A4',20.00,'orch front');
INSERT INTO Ticket values(123457, 'A5',20.00,'orch front');
INSERT INTO Ticket values(123457, 'A6',20.00,'orch front');

INSERT INTO TicketSale values(000001,'01-Oct-2014',40.00,'14-Oct',2014, 987654);
INSERT INTO Ticket values(000001, 'A1',20.00,'orch front');
INSERT INTO Ticket values(000001, 'A2',20.00,'orch front');

INSERT INTO TicketSale values(000002,'9-Oct-2014',60.00,'14-Oct',2014,123456);
INSERT INTO Ticket values(000002, 'A1',20.00,'orch front');
INSERT INTO Ticket values(000002, 'A2',20.00,'orch front');
INSERT INTO Ticket values(000002, 'A3',20.00,'orch front');

INSERT INTO DuesPayment values(11111, 2015, 50.00, '01-Jan-2015');
INSERT INTO DuesPayment values(22222, 2015, 50.00, '15-Jan-2015');
INSERT INTO DuesPayment values(33333, 2015, 50.00, '01-Feb-2015');
INSERT INTO DuesPayment values(44444, 2015, 50.00, '30-Jan-2015');
INSERT INTO DuesPayment values(55555, 2015, 50.00, '28-Jan-2015');

INSERT INTO Donation values(1234, '01-Mar-2015','sound board',1250.00,2015,'05-May');
INSERT INTO Donation values(1235, '15-Apr-2015','cash', 500.00,2015,'05-May');
INSERT INTO Donation values(1236, '05-May-2015','food',500.00,2015,'05-May');
INSERT INTO Donation values(1236, '06-May-2015','beverges',200.00,2015,'05-May');
INSERT INTO Donation values(1236, '07-May-2015','snacks',100.00,2015,'05-May');

INSERT INTO Member_Production values(11111,2015,'05-May','Emily','sets');
INSERT INTO Member_Production values(22222,2015,'05-May','Mrs. Webb','costumes');

-- DDL to delete all of the tables, use only if you need to rebuild the DB
DROP TABLE Member_Production;
DROP TABLE Ticket;
DROP TABLE Donation;
DROP TABLE DuesPayment;
DROP TABLE TicketSale;
DROP TABLE Performance;
DROP TABLE Production;
DROP TABLE Play;
DROP TABLE Subscriber;
DROP TABLE Sponsor;
DROP TABLE Member;
DROP DATABASE Theater;

Compile a list of your top-five personal guidelines for professionalism and ethics. This list should be a reflection of the values that you hold and how they are reflected in your professionalism. Your list must be thoughtful and detailed enough to show effort and authenticity in your guidelines. After stating each guiding ethic in your top-five list, include two sentences: 1. discussing why this is important to you, and 2. what it looks like to live that guideline out at work.

(a) List and explain three types of pipeline hazards.

(b) List and explain three types of cache misses.

(c) What is the principle of locality?

(d). Discuss the advantage and disadvantage of the write back and write through policy in the cache.

List the product name and description of every product with a list price higher than $500 that has either the word “humbuck” or “fret” in the description. Use a regular expression to do this.

Create a list of at least five different pricing strategies (from the long list of pricing strategies covered in this chapter) that you might want to use and why you think each strategy might be good for your business.

Explain and demonstrate a Linked List by adding following items into a Linked List: 10, 30, 15, 25 (show your work, you may write on paper and upload if you prefer)

This is an exercise on basic python grammar.

Instructions

Assume there are 10 teams in a competition. Their names are "team1", "team2" .. "team10".

Write a function to generate their order of performance randomly.

Submit your code in Jupyter notebook format (.ipynb) on Canvas

Hint

1. Use a list to save all the team names
2. Define a function called order()  
3. Use built-in module random to generate a random index within the list
4. Remove the item from the current list and add it to a new empty list
5. Repeat this until the old list is empty (length of the list becomes 0)

Note: You may certainly use another data structure or another algorithm without following the above suggested steps.

IN JAVA LANGUAGE

Linked List-Based Queue Implementation

Implement Queue using a Linked List.

• Use the language library LinkedList
• Queue methods will call the LinkedList methods
• You can use string as the object

Instead of using an array, as the QueueLab did, here you will use a Linked List from your language's library. Implement all the methods of Stack : enqueue(), dequeue(), size(), printQueue(), etc, using calls to the linked list methods that correspond to the actions need. In the array implementation, you wrote code to manipulate the array. For this linked list implementation, methods already exist.

Before the underlying implementation of queue was array, now the underlying implementation of queue will be Linked list.

IN JAVA LANGUAGE

Linked List-Based Stack Implementation

Implement Stack using a Linked List

• Use the language library LinkedList
• Stack methods will call the LinkedList methods
• You can use string as the object

Instead of using an array, as the StackLab did, here you will use a Linked List from your language's library. Implement all the methods of Stack : push(), pop(), size(), printStackDown(), etc, using calls to the linked list methods that correspond to the actions need. In the array implementation, you wrote code to manipulate the array. For this linked list implementation, methods already exist.

Before the underlying implementation of stack was array, now the underlying implementation of stack will be Linked list.