discussed with JOHN regarding my online exam tomorrow afternoon at 12:30 pm (Brisbane Australia). Here I'm uploading my course exercises and slides for expert.
slide excercises (1).docxThe Single Entity Slide Exercises · Visit www.marinetraffic.com/en/. · Select a port of interest to view the ships currently within its vicinity. · What technology is necessary to provide this information? · Automatic identification system (AIS) to capture data about the state of a ship · An communication channel to transmit the data (e.g., Inmarsat) · An AIS receiver to collect the data (e.g., Milltech Marine) · A database to record the data (e.g., MySQL) · A web interface to make the data publicly available (e.g., www.marinetraffic.com/en/) Design a data model for recording details of Olympic cities. Use MySQL Workbench to design your data model for recording details of Olympic cities. Create a table and add rows for the first three Olympics. Note: Make year an integer, because MySQL permits only years in the range 1901-2155 when the length is 4. INSERT INTO city (cityname, country, number, season, year, opendate, closedate) VALUES ('Athens', 'Greece', 1, 'Summer', 1896, '18960406', '18960415'); INSERT INTO city (cityname, country, number, season, year, opendate, closedate) VALUES ('Paris', 'France', 2, 'Summer', 1900, '19000514', '19001026'); INSERT INTO city (cityname, country, number, season, year, opendate, closedate) VALUES ('St. Louis', 'United States', 3, 'Summer', 1904, '19040829', '19040903'); Report the name and price of those shares where the share price is greater than 10. SELECT shrfirm, shrprice FROM share WHERE shrprice > 10; Calculate the total dividends earned by each share. Report the name of the firm and the payment sorted from highest to lowest payment. SELECT shrfirm, shrdiv*shrqty AS payment FROM share ORDER BY payment DESC; List names of shares whose name contains sheep or geese. SELECT shrfirm FROM share WHERE shrfirm REGEXP 'sheep|Sheep|geese|Geese'; The One-to-Many Relationship Slide Exercises Develop a data model to keep track of a distance runner’s times over various lengths. Create the database and add 3 rows for each of 2 athletes Report the total dividend payment for each country that has three or more stocks in the portfolio. SELECT natname, sum(stkdiv*stkqty*exchrate) AS payment FROM stock JOIN nation ON stock.natcode = nation.natcode GROUP BY natname HAVING COUNT(*) >= 3; Report the names of nations starting with ‘United’. SELECT natname FROM nation WHERE natname REGEXP '^United'; Report the country, firm, and stock holding for the maximum quantity of stock held for each country. SELECT natname, stkfirm, stkqty FROM stock JOIN nation ON stock.natcode = nation.natcode WHERE stkqty = (SELECT MAX(stkqty) FROM stock WHERE stock.natcode = nation.natcode); Create a view for dividend payment. CREATE VIEW divvalue (nation, firm, price, qty, exchrate, divpayment) AS SELECT natname, stkfirm, stkprice, stkqty, exchrate, stkdiv*stkqty*exchrate FROM stock JOIN nation ON stock.natcode = nation.natcode; A keen field hockey fan wants to keep track of which countries won which medals in the various summer Olympics for both the men’s and women’s events · Design a data model · Create the database · Populate with data for the last two Olympics Report all brown items that have been sold. SELECT itemname, itemcolor FROM item WHERE itemcolor = 'Brown' AND EXISTS (SELECT * FROM lineitem WHERE lineitem.itemno = item.itemno); Report all brown items that have not been sold SELECT itemname, itemcolor FROM item WHERE itemcolor = 'Brown' AND NOT EXISTS (SELECT * FROM lineitem WHERE lineitem.itemno = item.itemno); One-to-One and Recursive Relationships Slide Exercises Several Olympic events are team sports (e.g., basketball, relays) and some involve a pair of athletes (e.g., kayaking, rowing, beach volleyball) · A team can have a captain · A country has a flag bearer · There can be some husband and wife pairs at a games (e.g., Jared Tallent and Claire Woods from Australia) Draw a data model to record these details Find the names of employees in the same department as their boss. SELECT wrk.empfname, boss.empfname, boss.deptname FROM emp wrk JOIN emp boss ON wrk.bossno = boss.empno WHERE wrk.deptname = boss.deptname; Design a database to record details of all Olympic cities · Recognize that a city can host an Olympics more than once, though a particular Olympics is only in one city at a time · Recognize that each Olympics has only predecessor and successor Add details of the last three summer Olympics’ cities. Use SQL to determine which city was the host before London in 2008. INSERT INTO city (cityName, cityNation) VALUES ('Athens', 'Greece'); INSERT INTO city (cityName, cityNation) VALUES ('London', 'United Kingdom'); INSERT INTO city (cityName, cityNation) VALUES ('Beijing', 'China'); INSERT INTO olympics (olympicYear, olympicSeason, olympicNumber, cityName) VALUES (2004, 'Summer', 28, 'Athens'); INSERT INTO olympics (olympicYear, olympicSeason, olympicNumber, cityName, prior_olympicYear, prior_olympicSeason) VALUES (2008, 'Summer', 29, 'Beijing', 2004, 'Summer'); INSERT INTO olympics (olympicYear, olympicSeason, olympicNumber, cityName, prior_olympicYear, prior_olympicSeason) VALUES (2012, 'Summer', 30, 'London', 2008, 'Summer'); SELECT cityName FROM olympics WHERE olympicYear = (SELECT prior_olympicYear FROM olympics WHERE cityName = 'London' AND olympicYear = 2008); In a round-robin tournament, each contestant meets all other contestants in turn In the Olympics, it is common for an event with a large pool of contestants to be broken into groups, with a round-robin tournament in each group to determine who advances from the group to the next level Design a data model to record details of a round-robin competition Model the following situations · Friendship · Course prerequisites · A matrix organization where a person can report to multiple people Insert data in the round-robin database for the 2012 Football (Soccer) competition for Group A, with four teams How many ties were there in Group A? Use the ISO two-character country code to identify countries. INSERT INTO group (groupID) VALUES ('A'); INSERT INTO contestant (contestantID, groupID) VALUES ('AE', 'A'); INSERT INTO contestant (contestantID, groupID) VALUES ('GB', 'A'); INSERT INTO contestant (contestantID, groupID) VALUES ('SN', 'A'); INSERT INTO contestant (contestantID, groupID) VALUES ('UY', 'A'); INSERT INTO contest (contestantID1, contestantID2, score1, score2, contestDateTime) VALUES ('AE', 'UY', 1, 2, '20120726'); INSERT INTO contest (contestantID1, contestantID2, score1, score2, contestDateTime) VALUES ('GB', 'SN', 1, 1, '20120726'); INSERT INTO contest (contestantID1, contestantID2, score1, score2, contestDateTime) VALUES ('GB', 'AE', 3, 1, '20120729'); INSERT INTO contest (contestantID1, contestantID2, score1, score2, contestDateTime) VALUES ('SN', 'UY', 2, 0, '20120729'); SELECT count(*) FROM contest WHERE score1 = score2; Model a diagram. Slide Exercises Using the ClassicModels database, report the total value of payments for each customer to the nearest dollar and list in descending value SELECT customerName, FORMAT(SUM(amount),0) AS Payment FROM Customers JOIN Payments ON Customers.customerNumber = Payments.customerNumber GROUP BY CustomerName ORDER BY SUM(amount) DESC; Using the ClassicModels database, write a correlated subquery to determine which employees work in the Paris office SELECT CONCAT(firstName, ' ',lastName) FROM Employees WHERE EXISTS (SELECT * FROM Offices WHERE Offices.officeCode = Employees.officeCode AND city = 'Paris'); Write an SQL function to convert Fahrenheit to Celsius. CREATE FUNCTION F_to_C(F REAL) RETURNS REAL RETURN (F-32)*5/9; SELECT F_to_C(100); Spatial & Temporal Data Slide Exercises What is the area of Northern Ireland? SELECT ST_AREA(boundpath)*1406 as "Area (km^2)" from political_unit JOIN boundary ON unitname = 'Northern Ireland' WHERE political_unit.unitcode = boundary.unitcode; How close is the computed value to that reported in Wikipedia? Wikipedia reports 13,843 km2. A finer grid would increase accuracy. What is the eastern most city in Northern Ireland? SELECT east.cityname FROM city east JOIN political_unit ON east.unitcode = political_unit.unitcode WHERE unitname = 'Northern Ireland' AND NOT EXISTS (SELECT * FROM city other WHERE ST_X(other.cityloc) > ST_X(east.cityloc)); Modify the example geometry database design to include · Historic buildings in a city · Walking paths in a city · Use of the MULTIPOLYGON data type to indicate a political region’s boundary CREATE TABLE political_unit ( unitname VARCHAR(30), unitcode CHAR(2), unitpop DECIMAL(6,2), boundaries MULTIPOLYGON NOT NULL, PRIMARY KEY (unitcode)); CREATE TABLE city ( cityname VARCHAR(30), cityloc POINT NOT NULL, unitcode CHAR(2), historicbuildings MULTIPOINT NULL, walkingpaths MULTILINESTRING NULL, PRIMARY KEY (cityname, unitcode), CONSTRAINT fk_city_polunit FOREIGN KEY(unitcode) REFERENCES political_unit(unitcode)); lide Exercises A conglomerate requires each of its business units to submit an xml document at the end of each month reporting its revenue, costs, and people employed for the countries in which the unit operates. The currency of revenue and costs should be indicated using the ISO currency code. Design the schema. report.xsd Create an XML file containing data for three countries using the schema developed previously. report.xml The multinational company for which the schema was delivered previously operates in Australia, Bulgaria, India, Turkey, Moldova, Romania, USA, UK, and Uzbekistan. Edit the report schema to check that the currency code is for one of these countries. reportv2.xsd Use XSLT to create a html report of revenue for each country. report.xsl Organizational Intelligence Slide Exercises An international hotel chain has asked you to design a multidimensional database for its marketing department. What identifier and variable dimensions would you select? Apply the basic prompts outlined in the book. Identifiers When Arrival date and time Departure date and time Season level (high, medium, low) Weather Where Country Region City What Room type Hotel class (stars) How Reservation channel (phone, Web, mobile, fax) Variables Outcomes Duration of stay Room revenue Meals revenue Amenities revenue Compute total payments by country without and with ROLLUP SELECT DISTINCT country, FORMAT(SUM(amount),0) AS Payments FROM Customers, Payments WHERE Customers.customerNumber = Payments.customerNumber GROUP BY country; SELECT country, FORMAT(SUM(amount),0) AS Payments FROM Customers, Payments WHERE Customers.customerNumber = Payments.customerNumber GROUP BY country WITH ROLLUP;