IS335 UC Irvine Business information system DBMS and SQL Questions the exam has 2 short essay questions 1 excel questions 1 question of your choice bon
IS335 UC Irvine Business information system DBMS and SQL Questions the exam has
2 short essay questions
1 excel questions
1 question of your choice
bonus question Introduction to DBMS and SQL
https://www.w3schools.com/sql/default.asp
Data Analytics at a Glance!
11/19/19
2
Database Management System (DBMS)
■ Collection of interrelated data
■ Set of programs to access the data
■ DBMS contains information about a particular enterprise
■ DBMS provides an environment that is both convenient and
efficient to use.
■ Database Applications:
★ Banking: all transactions
★
★
★
★
★
Airlines: reservations, schedules
Universities: registration, grades
Sales: customers, products, purchases
Manufacturing: production, inventory, orders, supply chain
Human resources: employee records, salaries, tax deductions
■ Databases touch all aspects of our lives
11/19/19
3
Application Architectures
§Two-tier architecture: E.g. client programs using ODBC/JDBC to
communicate with a database
§Three-tier architecture: E.g. web-based applications, and
applications built using “middleware”
11/19/19
4
A Sample Relational Database
11/19/19
5
The Dangers in a Relational Database
• It looks very easy to use a RDBMS
– learning about normalization, SQL, etc. make for instant “experts.”
– lack of experience with major production systems can create
catastrophic project failures.
• Testing cycles are getting shorter
– newer development tools make software development quicker,
usually systems testing gets shortened.
• Recent college grads…
– least experienced developers usually have more training with
relational database technology.
– veteran developers are busy with older projects.
6
Sharing Knowledge and Success
• The Language of Oracle: SQL
– Structured Query Language, English like query language
– keywords: select, from, where, and order by
– Example query:
select city, temperature
from weather
where temperature > 80
order by temperature;
• Examples of relational databases
– stock tables in newspaper
– sports scores
– 100 year example in book
7
Relational Model
Attributes
■ Example of tabular data in the relational model
Customerid
customername
192-83-7465
Johnson
019-28-3746
Smith
192-83-7465
Johnson
321-12-3123
Jones
019-28-3746
Smith
11/19/19
customerstreet
customercity
accountnumber
Alma
Palo Alto
A-101
North
Rye
A-215
Alma
Palo Alto
A-201
Main
Harrison
A-217
North
Rye
A-201
8
Data Definition Language (DDL)
■ Specification notation for defining the database schema
★ E.g.
create table account (
account-number char(10),
balance
integer)
■ DDL compiler generates a set of tables stored in a data
dictionary
■ Data dictionary contains metadata (i.e., data about data)
★ database schema
★ Data storage and definition language
✔ language in which the storage structure and access methods
used by the database system are specified
✔ Usually an extension of the data definition language
11/19/19
9
Data Manipulation Language (DML)
■ Language for accessing and manipulating the data organized by
the appropriate data model
★ DML also known as query language
■ Two classes of languages
★ Procedural – user specifies what data is required and how to get
those data
★ Nonprocedural – user specifies what data is required without
specifying how to get those data
■ SQL is the most widely used query language
11/19/19
10
SQL
■ SQL: widely used non-procedural language
★ E.g. find the name of the customer with customer-id 192-83-7465
select customer.customer-name
from customer
where customer.customer-id = ‘192-83-7465’
★ E.g. find the balances of all accounts held by the customer with
customer-id 192-83-7465
select account.balance
from depositor, account
where depositor.customer-id = ‘192-83-7465’ and
depositor.account-number = account.account-number
■ Application programs generally access databases through one of
★ Language extensions to allow embedded SQL
★ Application program interface (e.g. ODBC/JDBC) which allow SQL
queries to be sent to a database
11/19/19
11
Database Users
■ Users are differentiated by the way they expect to interact with
the system
■ Application programmers – interact with system through DML
calls
■ Sophisticated users – form requests in a database query
language
■ Specialized users – write specialized database applications that
do not fit into the traditional data processing framework
■ Naïve users – invoke one of the permanent application programs
that have been written previously
★ E.g. people accessing database over the web, bank tellers, clerical
staff
11/19/19
12
Database Administrator
■ Coordinates all the activities of the database system; the
database administrator has a good understanding of the
enterprise’s information resources and needs.
■ Database administrator’s duties include:
★ Schema definition
★ Storage structure and access method definition
★ Schema and physical organization modification
★ Granting user authority to access the database
★ Specifying integrity constraints
★ Acting as liaison with users
★ Monitoring performance and responding to changes in
requirements
11/19/19
13
Transaction Management
■ A transaction is a collection of operations that performs a single
logical function in a database application
■ Transaction-management component ensures that the database
remains in a consistent (correct) state despite system failures
(e.g., power failures and operating system crashes) and
transaction failures.
■ Concurrency-control manager controls the interaction among the
concurrent transactions, to ensure the consistency of the
database.
11/19/19
14
Comparing SQL and NoSQL
11/19/19
15
Country
String
Afghanistan
Akrotiri
Albania
Algeria
American Samoa
Andorra
Angola
Anguilla
Antarctica
Antigua and Barbuda
Argentina
Armenia
Aruba
Ashmore and Cartier Islands
Australia
Austria
Azerbaijan
Bahamas The
Bahrain
Baker Island
Bangladesh
Barbados
Bassas da India
Belarus
Belgium
Belize
Benin
Bermuda
Bhutan
Bolivia
Bosnia and Herzegovina
Botswana
Bouvet Island
Brazil
British Indian Ocean Territory
British Virgin Islands
Brunei
Bulgaria
Burkina Faso
Burma
Burundi
Area(sq km) Birth rate(births/1000 population)
double
double
647500
47.02
123
28748
15.08
2381740
17.13
199
23.13
468
9
1246700
44.64
102
14.26
14000000
443
17.26
2766890
16.9
29800
11.76
193
11.26
5
7686850
12.26
83870
8.81
86600
20.4
13940
17.87
665
18.1
1
144000
30.01
431
12.83
0
207600
10.83
30528
10.48
22966
29.34
112620
41.99
53
11.6
47000
34.03
1098580
23.76
51129
12.49
600370
23.33
59
8511965
16.83
60
153
14.96
5770
19.01
110910
9.66
274200
44.17
678500
18.11
27830
39.66
Cambodia
Cameroon
Canada
Cape Verde
Cayman Islands
Central African Republic
Chad
Chile
China
Christmas Island
Clipperton Island
Cocos (Keeling) Islands
Colombia
Comoros
Congo Democratic Republic of the
Congo Republic of the
Cook Islands
Costa Rica
Cote d’Ivoire
Croatia
Cuba
Cyprus
Czech Republic
Denmark
Dhekelia
Djibouti
Dominica
Dominican Republic
East Timor
Ecuador
Egypt
El Salvador
Equatorial Guinea
Eritrea
Estonia
Ethiopia
Europa Island
#European Union
Falkland Islands (Islas Malvinas)
Faroe Islands
Fiji
Finland
France
181040
475440
9984670
4033
262
622984
1284000
756950
9596960
135
6
14
1138910
2170
2345410
342000
240
51100
322460
56542
110860
9250
78866
43094
131
23000
754
48730
15007
283560
1001450
21040
28051
121320
45226
1127127
28
3976372
12173
1399
18270
338145
547030
27.08
34.67
10.84
25.33
12.92
35.17
45.98
15.44
13.14
20.82
37.52
44.38
27.88
18.6
35.51
9.57
12.03
12.57
9.07
11.36
39.98
15.73
23.28
27.19
22.67
23.32
27.04
36.18
38.62
9.91
38.61
10.1
13.97
22.73
10.5
12.15
French Guiana
French Polynesia
French Southern and Antarctic Lands
Gabon
Gambia The
Gaza Strip
Georgia
Germany
Ghana
Gibraltar
Glorioso Islands
Greece
Greenland
Grenada
Guadeloupe
Guam
Guatemala
Guernsey
Guinea
Guinea-Bissau
Guyana
Haiti
Heard Island and McDonald Islands
Holy See (Vatican City)
Honduras
Hong Kong
Howland Island
Hungary
Iceland
India
Indonesia
Iran
Iraq
Ireland
Israel
Italy
Jamaica
Jan Mayen
Japan
Jarvis Island
Jersey
Johnston Atoll
Jordan
91000
4167
7829
267667
11300
360
69700
357021
239460
7
5
131940
2166086
344
1780
549
108890
78
245857
36120
214970
27750
412
0
112090
1092
2
93030
103000
3287590
1919440
1648000
437072
70280
20770
301230
10991
373
377835
5
116
3
92300
20.7
16.93
36.24
39.86
40.03
10.25
8.33
23.97
10.87
9.72
15.93
22.3
15.42
19.03
34.11
9.01
42.03
37.65
18.45
36.59
30.38
7.23
9.76
13.73
22.32
20.71
16.83
32.5
14.47
18.21
8.89
16.56
9.47
9.66
21.76
Juan de Nova Island
Kazakhstan
Kenya
Kingman Reef
Kiribati
Korea North
Korea South
Kuwait
Kyrgyzstan
Laos
Latvia
Lebanon
Lesotho
Liberia
Libya
Liechtenstein
Lithuania
Luxembourg
Macau
Macedonia
Madagascar
Malawi
Malaysia
Maldives
Mali
Malta
Man Isle of
Marshall Islands
Martinique
Mauritania
Mauritius
Mayotte
Mexico
Micronesia Federated States of
Midway Islands
Moldova
Monaco
Mongolia
Montserrat
Morocco
Mozambique
Namibia
Nauru
4
2717300
582650
1
811
120540
98480
17820
198500
236800
64589
10400
30355
111370
1759540
160
65200
2586
25
25333
587040
118480
329750
300
1240000
316
572
181
1100
1030700
2040
374
1972550
702
6
33843
2
1564116
102
446550
801590
825418
21
15.78
40.13
30.86
16.09
10.08
21.88
22.48
35.99
9.04
18.88
26.53
44.22
26.82
10.41
8.62
12.06
8.04
12
41.66
43.95
23.07
35.43
46.77
10.17
11.18
33.52
14.14
41.43
15.62
41.58
21.01
25.11
15.27
9.26
21.52
17.56
22.29
35.79
25.16
25.14
Navassa Island
Nepal
Netherlands
Netherlands Antilles
New Caledonia
New Zealand
Nicaragua
Niger
Nigeria
Niue
Norfolk Island
Northern Mariana Islands
Norway
Oman
Pakistan
Palau
Palmyra Atoll
Panama
Papua New Guinea
Paraguay
Peru
Philippines
Pitcairn Islands
Poland
Portugal
Puerto Rico
Qatar
Reunion
Romania
Russia
Rwanda
Saint Helena
Saint Kitts and Nevis
Saint Lucia
Saint Pierre and Miquelon
Saint Vincent and the Grenadines
Samoa
San Marino
Sao Tome and Principe
Saudi Arabia
Senegal
Serbia and Montenegro
Seychelles
5
140800
41526
960
19060
268680
129494
1267000
923768
260
35
477
324220
212460
803940
458
12
78200
462840
406750
1285220
300000
47
312685
92391
9104
11437
2517
237500
17075200
26338
410
261
616
242
389
2944
61
1001
1960582
196190
102350
455
31.45
11.14
15
18.49
13.9
24.88
48.3
40.65
19.51
11.67
36.73
30.42
18.37
19.96
29.95
29.43
20.87
25.31
10.78
10.82
13.93
15.54
19.26
10.7
9.8
40.6
12.33
18.12
20.05
13.83
16.34
15.95
10.18
40.8
29.56
35.21
12.12
16.22
Sierra Leone
Singapore
Slovakia
Slovenia
Solomon Islands
Somalia
South Africa
South Georgia and the South Sandwich Islands
Spain
Spratly Islands
Sri Lanka
Sudan
Suriname
Svalbard
Swaziland
Sweden
Switzerland
Syria
Taiwan
Tajikistan
Tanzania
Thailand
Togo
Tokelau
Tonga
Trinidad and Tobago
Tromelin Island
Tunisia
Turkey
Turkmenistan
Turks and Caicos Islands
Tuvalu
Uganda
Ukraine
United Arab Emirates
United Kingdom
United States
Uruguay
Uzbekistan
Vanuatu
Venezuela
Vietnam
Virgin Islands
71740
693
48845
20273
28450
637657
1219912
3903
504782
5
65610
2505810
163270
62049
17363
449964
41290
185180
35980
143100
945087
514000
56785
10
748
5128
1
163610
780580
488100
430
26
236040
603700
82880
244820
9631418
176220
447400
12200
912050
329560
352
42.84
9.49
10.62
8.95
30.74
45.62
18.48
10.1
15.63
35.17
18.39
27.72
10.36
9.77
28.29
12.64
32.58
38.16
15.7
33.48
25.18
12.81
15.5
16.83
27.68
22.23
21.91
47.39
10.49
18.78
10.78
14.14
14.09
26.22
23.06
18.91
17.07
14.2
Wake Island
Wallis and Futuna
West Bank
Western Sahara
Yemen
Zambia
Zimbabwe
7
274
5860
266000
527970
752614
390580
32.37
43.07
41.38
29.74
Current account balance Death rate(deaths/1000 population) Debt – external
double
double
double
20.75
8000000000
-504000000
11900000000
5.12
4.6
3.33
6.07
25.9
5.43
1410000000
21900000000
5.44
7.56
8.16
6.57
231000000
1.577E+11
905000000
285000000
7.44
9.7
9.86
8.97
4.08
3.087E+11
15500000000
1832000000
308500000
6215000000
216600000
8.4
9.17
19970000000
668000000
-1119000000
11400000000
-115000000
-159900000
273000000
-2100000000
337000000
14.15
10.22
6.04
13.76
7.63
12.94
7.64
8.44
29.36
600000000
28300000000
1362000000
1600000000
160000000
245000000
5439000000
3000000000
531000000
8000000000
6.15
2.198E+11
682900000
-471700000
-185000000
-59500000
4.42
3.42
14.26
18.86
12.15
17.43
36100000
0
16100000000
1300000000
6752000000
1133000000
-37880000
5473000000
-240400000
-38300000000
-3283000000
-2899000000
586100000
10450000000
8800000
-316200000
-149100000
28200000000
-93760000
8.97
15.4
7.73
6.62
4.81
20.27
16.41
5.76
6.94
2400000000
8460000000
5.7E+11
325000000
70000000
881400000
1100000000
44600000000
2.333E+11
266000000
5.59
8.4
14.43
14.82
-980300000
-421500000
-1925000000
-185100000
-619900000
-5730000000
6529000000
4.33
14.94
11.38
7.19
7.64
10.54
10.43
38700000000
232000000
11600000000
5000000000
141000000
5962000000
11810000000
26400000000
12090000000
7327000000
36280000000
21700000000
330200000
2185000000
30320000000
-1706000000
762200000
261100000
2113000000
-880500000
-578600000
-144900000
-1169000000
-464400000
19.39
6.81
7.35
6.3
4.24
5.26
5.85
12
13.53
13.21
15.06
366000000
161500000
7745000000
16810000000
33750000000
4792000000
248000000
311000000
8373000000
2900000000
10.1
11390000000
-305000000
8.69
5.65
9.79
9.08
64000000
188100000
30000000000
4.85
4.63
1200000000
11.72
11.81
3.87
9.09
10.55
10.84
9.18
3804000000
476000000
108000000
1800000000
67230000000
25000000
196000000
-129400000
-27630000
10.15
7.77
7.17
6.06
4.41
6.81
9.95
15.38
16.53
8.32
12.34
258300000
14850000000
6.87
5.98
5365000000
4.176E+11
-7941000000
-570000000
4897000000
7338000000
2100000000
-560000000
-2881000000
211900000
-21100000000
-830700000
13.19
6.68
8.28
6.25
5.55
5.49
7.85
6.18
10.3
5.37
57000000000
3073000000
1.172E+11
1.415E+11
13400000000
1.25E+11
11000000000
74460000000
9.139E+11
5964000000
1.702E+11
8.95
196800000
-16400000
-632900000
73590000000
83870000
-8000000000
-1381000000
-308300000
7396000000
5969000000
3250000000
941500000
1200000000
1200000000
9.19
203200000
2.63
7320000000
-39020000
-459200000
26780000000
12040000000
-87920000
-80760000
-1251000000
-2389000000
-108300000
9895000000
-1600000000
-311000000
-281900000
-55500000
11810000000
-241000000
284100000
-4113000000
-148400000
765400000
-101200000
234300000
9.46
14.65
26030000000
6792000000
8.37
7.05
6.26
2.42
7.13
11.83
13.7
6.24
25.03
17.87
3.48
7.06
10.92
8.41
4.23
8.73
11.35
23.39
5.06
7.24
19.05
8
11.26
4.88
6.44
12.44
6.83
7.9
4.73
4.87
10000000
12000000000
1.6E+11
15020000000
1970000000
2490000000
7368000000
15840000000
735000000
2100000000
4069000000
0
10010000000
12.79
12.71
7.03
7.17
5.64
20.99
18.36
6.82
1400000000
18000000000
1191000000
8900000
17070000000
966000000
1136000000
33300000
2700000000
1863000000
4600000000
3129000000
53360000000
281000000
3300000000
130000000
86500000
180000000
2500000000
1780000000
1.499E+11
53100000
19900000000
-3647000000
-843100000
5228000000
9.47
8.68
6.41
5.65
7.53
4.49
21.33
17.18
2700000000
1350000000
79000000
47340000000
4573000000
1600000000
30550000000
418000
30520000000
2674000000
1400000000
2.3
9.45
3.86
8.45
6.85
0
4814000000
33970000000
0
-469600000
29150000
-36110000
-30000000
3600000000
6.54
7.37
4.53
6.26
5.47
8780000000
2463000000
3239000000
29790000000
55600000000
-3831000000
-8120000000
10.01
10.43
7.86
4.61
5.48
11.74
14.52
16.32
6.43
8.47
5.12
6.7
6
6.54
8.07
6.68
2.62
10.6
10.49
6.34
99150000000
2.747E+11
5187000000
-3631000000
46040000000
-212500000
-31500000
51500000000
-518800000
-3008000000
-98420000
18620000000
24590000000
1.696E+11
1300000000
171000000
214000000
167200000
197000000
318000000
34350000000
3476000000
12970000000
218100000
-2480000000
20.61
4.16
9.43
10.22
3.98
16.97
21.32
1500000000
19400000000
19540000000
14650000000
180400000
3000000000
27010000000
-30890000000
9.63
7.711E+11
-587300000
-763600000
6.49
9.16
7.16
10850000000
21000000000
321000000
-82400000
24080000000
40950000000
1100000000
21160000000
-52000000
-327400000
6736000000
-125600000
25.26
10.36
8.48
4.88
6.38
8.39
16.71
7.02
11.8
320000000
66500000000
1548000000
5.35
9.37
8800000000
-1400000000
-51640000
71850000
-15300000000
114000000
-590800000
4584000000
6300000000
-33460000000
-6.465E+11
181800000
461900000
14590000000
-2061000000
5.09
5.96
8.78
4.28
7.22
12.8
16.42
4.26
10.18
8.25
9.06
7.95
7.9
4.9
6.2
6.26
4000000000
55500000000
888000000
7321000000
50590000000
1400000000
0
63400000
2940000000
14710000000
16900000000
2400000000
3865000000
16370000000
5900000000
4.71E+12
1.4E+12
12800000000
4351000000
83700000
33290000000
16550000000
369900000
-181400000
-230300000
3.99
108000000
8.53
20.23
24.66
5400000000
5353000000
4086000000
Electricity – consumption(kWh) Electricity – production(kWh) Exports
GDP
double
double
double
double
652200000
540000000
446000000 21500000000
6760000000
23610000000
120900000
1587000000
42600000
5680000000
552400000 17460000000
25760000000 32160000000
2.123E+11
130000000
30000000
500000000
58000000 1900000000
1707000000 12760000000 23170000000
2600000
112000000
103000000
81650000000
5797000000
751200000
110800000
689000000
750000000
81390000000 33780000000
4.835E+11
6492000000
850000000 13650000000
807700000
128000000 1940000000
1.956E+11
55090000000
17370000000
1596000000
6379000000
2.103E+11 86890000000
6.117E+11
58490000000
1.027E+11
2.559E+11
17550000000 3168000000 30010000000
1716000000
636000000 5295000000
6860000000 8205000000 13010000000
15300000000
744000000
16450000000
800000000
34300000000
78820000000
108800000
565200000
598000000
312900000
3848000000
8318000000
1890000000
30000000000 11470000000 70500000000
76580000000
2.557E+11
3.162E+11
117000000
401400000 1778000000
285200000
720900000 8338000000
643000000
879000000 2330000000
2001000000
154000000 2900000000
4132000000 1986000000 22330000000
10040000000 1700000000 26210000000
930000000 2940000000 15050000000
3.519E+11
33740000
2286000000
32710000000
335700000
3484000000
137800000
7478000000
206000000
3.39E+11 95000000000
36280000
2458000000
43070000000
361000000
5068000000
132000000
2.757E+11
4569000000
1.492E+12
25300000 2498000000
7700000000 6842000000
9134000000 61630000000
418600000 15740000000
2137000000 74300000000
31840000 4001000000
100600000
3321000000
4.873E+11
40060000
382100000
98580000
89400000
41800000000
1.63E+12
122000000 2311000000 26990000000
3571000000 2445000000 30170000000
5.489E+11
3.156E+11
1.023E+12
43080000
61110000
600000000
410800000
1200000 1391000000
106000000
172000000 4248000000
96130000
365000000 15660000000
48600000000 29200000000
1.691E+11
1.91E+12
5.831E+11
7.262E+12
41140000000
22170000
4168000000
573600000
25110000
5733000000
2976000000
15200000000
13400000000
602000000
55330000000
31630000000
44870000000 15500000000
2.811E+11
23840000
28000000
441000000
6086000000 1417000000 42740000000
348000000 2224000000 2324000000
27000000
9100000
105000000
6614000000 6184000000 37970000000
4759000000 5124000000 24780000000
12510000000 7845000000 50330000000
14410000000 2104000000 33920000000
4000000000
49300000 4540000000
71750000000 66510000000
1.722E+11
36380000000 73060000000
1.744E+11
167400000
63620000
8912000000
180000000
68410000
9583000000
75580000000
75580000000
4450000000
24820000
229400000
6358000000
1998000000
81270000000
81270000000
4158000000
26690000
246600000
8301000000
2149000000
155000000
39000000
5446000000
8000000
7560000000
11000000000
3249000000
2771000000
64440000
5701000000
562800000
619000000
384000000
55680000000
370000000
49510000000
3.163E+11
32350000000
1270000000
4154000000
19230000000
54890000000
2.661E+12
17720000
204600000
697500000
78580000000
4.147E+11
2.888E+12
1.109E+12
19060000
82000000
220000000
408000000
750000000
609000000
71590000000 61040000000
5.286E+11
4.19E+11
1.165E+13
75000000
1000000000
5173000000
1.512E+11
1.737E+12
427900000
353400000
460100000
380000000
1080000000
83990000
1161000000
90310000
6811000000
5.195E+11
6137000000
96760000
673200…
Purchase answer to see full
attachment
