Description

University of London

Computing and Information Systems/Creative Computing

CO2209 Database systems

Coursework assignment 1 2019-2020

ANSWER SHEET

Your coursework assignment should be submitted as a single PDF file, using the following file-naming conventions:

YourName_SRN_CO2209cw#.pdf  

Example: GraceHopper_920000000_CO2209cw1.pdf)

• YourName is your full name as it appears on your student record (check your student portal); Please use just your name, and not any appellation such as ‘MR’ or ‘MS’.
• SRN is your Student Reference Number, for example 920000000;
• CO2209 is the course number for this course (databases).
• cw# is either cw1 (coursework 1) or cw2 (coursework 2).

A – Essay background in databases, any problems installing MariaDB or MySQL.

My experience with a database was an inhouse database at the bank which deals with the storing and reporting of compromise debit/credit cards. The database stored truncated card numbers, date of fraudulent transactions (year, day, and month), P.O.S terminals or website where the transactions were processed, the amount in dollar value of the compromise debit/credit card and lastly the different stage of resolutions process. The main purpose of that database was to keep track of loses occurred on cards, to give an overview of the total amount of cards that were compromise compare to the bank total debit/card portfolio, funds that were recovered and identify the merchants( P.O.S terminals , website) were fraudulent transaction occurred more often than usual. The information produce by the data above are taken into consideration when restrictions, changes, triggers (debit/credit card alerts) are being implemented.

[1 hour - 4 marks]

B – Data

B1 – How examples A and C differ from B

The database is consist of data , in the example stated in Example A , B & C share the same data but structure differently. The computer system can make changes  A or C rather than B because of the structure of A or C, for eg adding additional data, updating data or deleting data can be done a row at a time since the data uniquely structure in rows or lines. Any changes in example B for eg deleting data can affect the essay in its entirety.

[1 hour - 4 marks]

B2 – Which level is being referred to?

1.   external level

2.  internal level

3 internal level *

4.  external level

[1 hour - 4 marks]

B3 – SQL data types

(1)   Smallint – ‘a small integer The signed range is -32768 to 32767. The unsigned range is 0 to 65535’.

(2)   Mediumint - A medium-sized integer. The signed range is -8388608 to 8388607. The unsigned range is 0 to 16777215

(3)   Bigint- A large integer. The signed range is -9223372036854775808 to 9223372036854775807. The unsigned range is 0 to 18446744073709551615.

(4)   Decimal- A packed "exact" fixed-point number.

(5)   Char- A fixed-length string that is always right-padded with spaces to the specified length when stored.

(6)   Varchar- A variable-length string. M represents the maximum column length in characters. The range of M is 0 to 65,532. 

(7)   Date- A date. The supported range is '1000-01-01' to '9999-12-31'.

(8)   Datetime- A date and time combination.

(9)   Timestamp- With a single argument, this function returns the date or datetime expression expr as a datetime value. 

(10) Time- A time. The range is '-838:59:59.999999' to '838:59:59.999999'.

CHANGE TO MY SQL