ComputerScienceExpert
$18/per page/
DATABASE
Question 1.
1. This question is based on the San Francisco Crime Data file, (see TABLE A below).
1a. Create an ERD for San Francisco Crime Data.
1b. Create a database in Oracle Database for the San Francisco Crime Data file. Load the data. Show that the database has been created. Include scripts and also output logs and screen shots.
1c. Do a query to show what crimes occurred on January 3rd, 2006. Show the query as well as the output with a screen shot.
1d. Do a query to show the beat where the crime was committed on January 3rd, 2006.
Â
TABLE A
Â
|
cdatetime |
address |
district |
beat |
grid |
crimedescr |
ucr_ncic_code |
latitude |
longitude |
|
1/19/06 17:17 |
16TH ST / P ST |
3 |
3A |
766 |
10.48.150 E FALSIFY PKG PERMIT |
7399 |
38.571472 |
-121.48809 |
|
1/1/06 8:05 |
SWEET PEA WAY / SHADY ARBOR CT |
1 |
1B |
473 |
10851 VC AUTO THEFT LOCATE |
2404 |
38.607584 |
-121.541105 |
|
1/1/06 23:05 |
PRINCETON ST / GLENROSE AVE |
2 |
2C |
567 |
10851 VC AUTO THEFT LOCATE |
2404 |
38.614729 |
-121.430007 |
|
1/1/06 23:16 |
LEXINGTON ST / FRIENZA AVE |
2 |
2B |
564 |
10851 VC AUTO THEFT LOCATE |
2404 |
38.612927 |
-121.44157 |
|
1/2/06 9:56 |
ROANOKE AVE / BELDEN ST |
2 |
2A |
514 |
10851 VC AUTO THEFT LOCATE |
2404 |
38.634823 |
-121.441668 |
|
1/2/06 11:32 |
MCNAMARA WAY / BENOIT CT |
5 |
5B |
1618 |
10851 VC AUTO THEFT LOCATE |
2404 |
38.462529 |
-121.458504 |
|
1/2/06 12:29 |
1960 JOAN WAY |
2 |
2C |
606 |
10851 VC AUTO THEFT LOCATE |
2404 |
38.607395 |
-121.420595 |
|
1/2/06 21:13 |
2224 BELL AVE |
2 |
2A |
237 |
10851 VC AUTO THEFT LOCATE |
2404 |
38.646887 |
-121.414066 |
|
1/3/06 0:09 |
JIBBOOM ST / RICHARDS BLVD |
3 |
3A |
721 |
10851 VC AUTO THEFT LOCATE |
2404 |
38.596674 |
-121.505698 |
|
1/3/06 0:38 |
19TH ST / CAPITOL AVE |
3 |
3M |
746 |
10851 VC AUTO THEFT LOCATE |
2404 |
38.573722 |
-121.482673 |
|
1/3/06 3:07 |
6230 GREENHAVEN DR |
4 |
4C |
1265 |
10851 VC AUTO THEFT LOCATE |
2404 |
38.513822 |
-121.526923 |
|
1/3/06 7:57 |
61ST ST / 19TH AVE |
6 |
6B |
1067 |
10851 VC AUTO THEFT LOCATE |
2404 |
38.534942 |
-121.433598 |
|
1/3/06 8:02 |
SELMA ST / DIXIEANNE AVE |
2 |
2C |
604 |
10851 VC AUTO THEFT LOCATE |
2404 |
38.609294 |
-121.440416 |
|
1/3/06 8:21 |
15 BUSINESS PARK WAY |
6 |
6C |
1407 |
10851 VC AUTO THEFT LOCATE |
2404 |
38.518397 |
-121.396572 |
|
1/3/06 8:57 |
1028 LAKE GLEN WAY |
4 |
4C |
1225 |
10851 VC AUTO THEFT LOCATE |
2404 |
38.518969 |
-121.520539 |
|
1/3/06 9:41 |
2420 S ST |
3 |
3B |
778 |
10851 VC AUTO THEFT LOCATE |
2404 |
38.564804 |
-121.478132 |
|
1/3/06 9:41 |
2681 FAIRFIELD ST |
2 |
2B |
552 |
10851 VC AUTO THEFT LOCATE |
2404 |
38.616117 |
-121.452931 |
|
1/3/06 14:09 |
EMMONS ST / MACARTHUR ST |
2 |
2A |
236 |
10851 VC AUTO THEFT LOCATE |
2404 |
38.644269 |
-121.427487 |
|
1/3/06 16:52 |
STANFORD AVE / EDGEWATER RD |
2 |
2C |
601 |
10851 VC AUTO THEFT LOCATE |
2404 |
38.609181 |
-121.461814 |
Question 2
2. Using the data given in the attached Excel file (TABLE B), show us what a DDL is and a DML. Use the data given to demonstrate both commands. You will need to use Oracle Database 11g to answer these questions.
2a. Explain what DDL is and also what DML is.
2b. Use examples of DDL as a demo of what you can do with the data given
2c. Use examples of DML to demo what you can do with the data given
2d. Do a search to find out how many people joined the company on 06/17/2011.
TABLE B
|
lastname |
firstname |
|
date |
|
Franklin |
Acton |
<..t@faucibus.co.uk |
03/10/11 |
|
Pace |
Callie |
<..c@Phasellusvitaemauris.edu |
07/10/11 |
|
Barker |
Lunea |
<..m@eratVivamusnisi.com |
07/15/11 |
|
Navarro |
Rina |
<..a@dolorQuisque.ca |
03/13/11 |
|
Holcomb |
Merrill |
<..i@habitantmorbi.com |
07/23/11 |
|
Hamilton |
Nichole |
<..a@cursus.org |
06/28/11 |
|
Benton |
Karyn |
<..m@odiosempercursus.com |
03/29/2015 |
|
Holden |
Maite |
<..e@vehiculaaliquetlibero.ca |
06/05/2015 |
|
Butler |
Dustin |
<..m@enimEtiam.ca |
12/07/2015 |
|
French |
Cody |
<..s@ultriciessemmagna.net |
01/04/11 |
|
Garrison |
Kyla |
<..s@convallisdolor.ca |
05/18/2015 |
|
Hensley |
Aristotle |
<..c@non.co.uk |
07/31/2015 |
|
Hopper |
Tasha |
<..a@imperdietnonvestibulum.net |
12/13/2015 |
|
Daniels |
Kato |
<..i@ideratEtiam.org |
11/13/2015 |
|
Armstrong |
Dahlia |
<..e@aauctornon.co.uk |
09/07/2015 |
|
Rivers |
Ifeoma |
<..e@fermentum.org |
10/19/11 |
|
Poole |
Iliana |
<..t@metussit.org |
06/21/11 |
|
Shaw |
Joseph |
<..e@Proindolor.edu |
06/27/11 |
|
Cardenas |
Arden |
<..n@euturpis.co.uk |
03/19/11 |
|
Case |
Melyssa |
<..o@velvulputateeu.edu |
04/01/11 |
|
Tucker |
Galena |
<..m@liberoProin.ca |
02/07/11 |
|
Matthews |
Uriah |
<..s@tristiquepharetra.org |
05/20/11 |
|
Park |
Colt |
<..s@bibendumfermentum.com |
12/29/11 |
|
Mcmillan |
Zorita |
<..s@euismodestarcu.org |
11/12/2015 |
|
French |
Paul |
<..r@dignissimMaecenas.ca |
10/18/11 |
|
Mathis |
Ashely |
<..s@enimnonnisi.edu |
07/07/11 |
|
Byrd |
Sonia |
<..t@Integeridmagna.co.uk |
12/05/2015 |
|
Massey |
Jerome |
<..m@vitae.net |
12/14/11 |
|
Howard |
Zeph |
<..s@urnaVivamusmolestie.net |
01/11/11 |
|
Mckay |
Dawn |
<..r@ligulaAeneangravida.co.uk |
06/04/2015 |
|
Dominguez |
Vera |
<..t@Duisat.org |
10/21/11 |
|
Dunlap |
Hadassah |
<..s@acmattis.ca |
07/13/2015 |
|
Hebert |
Gwendolyn |
<..s@nibhdolornonummy.com |
07/25/11 |
|
Duke |
Jane |
<..a@Praesentluctus.edu |
02/19/11 |
|
Ray |
Dale |
<..t@eumetusIn.co.uk |
04/13/2015 |
|
Torres |
Salvador |
<..e@consequat.ca |
11/18/2015 |
|
Emerson |
Irene |
<..t@eteros.co.uk |
12/24/11 |
|
Frank |
Elton |
<..t@Morbiquisurna.co.uk |
11/08/2015 |
|
Travis |
Carol |
<..t@molestiepharetra.ca |
09/09/2015 |
|
Brock |
Kermit |
<..c@quam.org |
08/08/2015 |
|
Avery |
Mannix |
<..e@elitpellentesque.com |
10/23/11 |
|
Vaughn |
Cyrus |
<..e@Proin.ca |
09/17/2015 |
|
Dillon |
Lavinia |
<..d@morbi.edu |
02/23/11 |
|
Norman |
Patrick |
<..e@musProinvel.com |
02/15/2017 |
|
Sweeney |
Sage |
<..s@rutrumeu.edu |
08/05/11 |
|
House |
Bruno |
<..n@cursuset.org |
06/05/2015 |
|
Downs |
Lucius |
<..s@laciniamattis.co.uk |
07/24/11 |
|
Roberts |
Dane |
<..i@Cras.org |
09/05/11 |
|
Fuentes |
Fiona |
<..n@metus.com |
11/29/11 |
|
Riggs |
Berk |
<..u@sit.com |
01/11/11 |
|
Morrison |
Alfonso |
<..m@luctuset.co.uk |
10/15/2015 |
|
Castaneda |
Neve |
<..t@interdumliberodui.net |
06/17/11 |
|
Lewis |
Flynn |
<..i@Vivamuseuismodurna.co.uk |
07/12/2015 |
|
Bridges |
Jamalia |
<..u@porttitorvulputate.net |
12/07/2015 |
|
Kirby |
Benjamin |
<..m@lorem.org |
09/21/11 |
|
Hampton |
Nayda |
<..t@Aliquamornare.net |
11/02/11 |
|
Gutierrez |
Carol |
<..t@eunibhvulputate.net |
09/06/11 |
|
Joyner |
Dalton |
<..t@auctor.com |
11/11/2015 |
|
Black |
Lunea |
<..a@scelerisquenequesed.ca |
07/02/11 |
|
Craig |
Marvin |
<..t@elementum.org |
03/02/2017 |
|
Cook |
Iliana |
<..d@eleifendnec.net |
10/03/11 |
|
Glenn |
Latifah |
<..n@Nullam.co.uk |
01/15/2017 |
|
Richardson |
Madison |
<..t@iaculisquispede.ca |
03/08/2015 |
|
Cleveland |
Brock |
<..t@lobortisquispede.ca |
08/26/11 |
|
Wolf |
Alice |
<..e@natoque.org |
03/04/11 |
|
Waller |
Ciaran |
<..h@Donectempuslorem.net |
04/14/11 |
|
Farley |
Nigel |
<..s@etrutrum.com |
04/29/11 |
|
Chase |
Jorden |
<..c@nec.ca |
06/24/11 |
|
Estes |
Eric |
<..t@velitSed.ca |
06/24/11 |
|
Mooney |
Hammett |
<..a@pretiumneque.org |
02/05/2017 |
|
Hopper |
Dante |
<..s@nequetellusimperdiet.org |
08/22/11 |
|
Mosley |
Silas |
<..e@turpisNullaaliquet.net |
10/31/2015 |
|
Perkins |
Flavia |
<..m@ligulaeuenim.edu |
04/10/2015 |
|
Pena |
Zachary |
<..m@duisemper.co.uk |
11/28/2015 |
|
Holman |
Belle |
<..s@mauris.co.uk |
07/17/2015 |
|
Pollard |
Audra |
<..m@vitaeodiosagittis.com |
09/15/2015 |
|
Morrison |
Rebecca |
<..s@vehiculaPellentesque.edu |
08/03/11 |
|
Gonzalez |
Gay |
<..e@tristique.edu |
08/06/11 |
|
Faulkner |
Nathaniel |
<..m@sitamet.net |
09/09/11 |
|
Rich |
Driscoll |
<..d@arcuiaculisenim.ca |
08/25/2015 |
|
Orr |
Anastasia |
<..u@semPellentesqueut.ca |
09/11/11 |
|
Oneil |
Haley |
<..t@odiosempercursus.co.uk |
06/17/11 |
|
Matthews |
Marshall |
<..s@at.ca |
12/18/11 |
|
Graham |
Hannah |
<..u@urnanecluctus.edu |
04/15/2015 |
|
Hendrix |
Tanya |
<..o@turpisAliquam.edu |
03/25/2015 |
|
Todd |
Vaughan |
<..a@turpisnon.org |
09/08/2015 |
|
Sparks |
Clarke |
<..r@massa.com |
05/03/11 |
|
Dickerson |
Acton |
<..s@facilisisfacilisis.net |
09/30/11 |
|
Meyers |
Harding |
<..s@turpis.co.uk |
07/01/2015 |
|
West |
Noelle |
<..s@risusQuisque.co.uk |
02/26/2017 |
|
Gross |
Stella |
<..c@faucibusut.co.uk |
02/11/11 |
|
Shepard |
Declan |
<..a@ornare.org |
03/17/11 |
|
Houston |
Thor |
<..a@ullamcorpervelitin.ca |
07/22/11 |
|
Edwards |
Magee |
<..u@famesac.net |
09/15/11 |
|
Alexander |
Blake |
<..a@odio.org |
05/22/11 |
|
Cole |
McKenzie |
<..d@sitametconsectetuer.org |
12/25/11 |
|
Garrison |
Magee |
<..e@urna.com |
03/27/11 |
|
Dickerson |
Noelani |
<..s@etarcu.org |
04/12/11 |
|
Osborne |
Steven |
<..t@erosnon.ca |
05/19/11 |
|
Williams |
Beau |
<..d@eteros.ca |
04/01/2015 |