Two Main Reasons I Have Chosen Mysql Computer Science Essay

Published:

This essay has been submitted by a student. This is not an example of the work written by our professional essay writers.

Chosen MySQL as my main database for this coursework as it is recommended by the school. Apart from that reason it also because most of my friends and course mates are choosing it as well. If more people around my surrounding using the same database as me, learning will be much easier and more fun. Anything unsure of can turn to the lecturer but of cause getting help from class mates will be much faster as the lecturer may sometimes tend to be out of reach as there are many other students like me.

So basically that is the two main reasons I have chosen MySQL.

When downloading and installing of MySQL, it went through like a breeze, no issue with it and it was easier than I was expecting. Though the installing is a little long as there are many steps to follow, like naming the server and setting up password but it still went through with no errors.

As for the last part of downloading mondial database and the inputs from the given links, it is the same as before without errors. But this time round it took a while for me to look through the MySQL and setting up the query script then pasting the SQL statements into the database. Executed the both scripts and it took longer for the input script to execute.

After query completed, I created a new query script and using the SELECT statement to populate the table that was in the mondial script. An output screen will be displayed on MySQL and showed the table that I enquired on.

A (2)

User's Manual Summary

3. Tutorial

3.1. Connecting to and Disconnecting from the Server: It teaches how to connect and disconnect from the server by typing in hostname and password. It also shows how to rectify issue when user not able to connect to the server.

3.2. Entering Queries: How to create tables, input data and retrieve data. It also explained the sql command prompt.

3.3. Creating and Using a Database: It explains how to use the SELECT statement to create tables and INSERT/LOAD statement to input data into the created table. It also show how to manipulate the data in the table to display what is expected of the user, like showing everything in a table, particular row/column, sorting and counting rows.

3.4. Getting Information About Databases and Tables: It basically tell the user how to get information of the databases or tables as user may tend to forget the names for it when user has many to handle.

3.6. Examples of Common Queries: It explains how to find data within the database. For user would like to find the maximum or minimum of certain rows and columns. It also teaches us how to join table with foreign key and get the data that is required.

4. MySQL Programs

4.2. Using MySQL Programs: It explains how the SQL programs are requested for. It also teaches some basic command prompt for MySQL. Regarding the connecting to the server, it shows us what the required parameters are and you can override them with the use of program options.

4.3. MySQL Server and Server-Startup Programs: This section explains the mysqld and the various startup scripts. It also explains the different program options available.

4.5. MySQL Client Programs: This section explains the command line tool and some basic commands like creating table, drop table and etc. It also shows how to back up a database and importing data.

4.8. Miscellaneous Programs: This section explains the error code that will be displayed when there is a system error.

5. MySQL Server Administration

5.1. The MySQL Server: Configuration and commands option; using system variables; SQL mode and user-defined partitioning.

5.2. MySQL Server Logs: Output destinations; different types of log; clean up the server log so it does not takes up too much disk space.

5.3. Running Multiple MySQL Instances on One Machine: Run and setting up multiple databases on one machine; connect with client program to the server.

6. Security

6.1. General Security Issues: security within MySQL; factors that affect security; access control and network control; proper back up should be done.

6.2. The MySQL Access Privilege System: Privilege system by MySQL; permission grant; specified username and hostname can let users have the same name connect to different hosts; causes of access-denied errors.

6.3. MySQL User Account Management: Usernames and passwords; adding and removing of user accounts; limits number of existing connections that can be made; secure connections with SSL; remote connection;

7. Backup and Recovery

7.1. Backup and Recovery Types: The various options in backup from local disk verses remotely; online verses offline; recovery of data.

7.2. Database Backup Methods: The different backup methods; making backups with mysqldump or mysqlhotcopy; recovering corrupt tables.

7.3. Example Backup and Recovery Strategy: Steps to recover data from backups; backup policy; using backups for recovery.

7.4. Using mysqldump for Backups: Describes how mysqldump produce dump files; create SQL-format backups; reload dump files; delimited-text format backups.

7.6. MyISAM Table Maintenance and Crash Recovery: How to use myisamchk to check or repair MyISAM table; crash recovery; using of commands to check and repair MyISAM table for errors; table maintenance.

8. Optimization

8.1. Optimization Overview: Optimizing the database level by ensuring whether the tables are structured properly, right data types; optimizing the hardware level.

8.2. Optimizing SQL Statements: Making use of SELECT statement to save time; the speed of different statements; complex privilege setup will have more overhead to all SQL statements; INFORMATION_SCHEMA table can be executed more quickly for certain queries; miscellaneous tips for improving query processing speed.

8.3. Optimization and Indexes: Using of indexes; how indexes can find data quickly in databases; include primary and foreign key for faster query performance; multiple-column indexes; check indexes usage.

8.4. Optimizing Database Structure: Efficient ways of organizing tables and column; minimize disk space; using numeric data; disadvantages of creating too many tables.

8.5. Optimizing for InnoDB Tables: Storage layout; compressed row format; ideal balance of performance and workload for the server; load bulk data; DDL operations on tables and indexes; different configurations to optimize the InnoDB.

8.6. Optimizing for MyISAM Tables: Use analyze table to optimize the queries; load bulk data; repair table.

8.7. Optimizing for MEMORY Tables: Using memory table for less important data; recommendation to achieve best performance with memory table.

8.8. Understanding the Query Execution Plan: Set of operations that the optimizer will choose to provide the most efficiency; how to use the EXPLAIN statement; output format for the EXPLAIN statement; estimating the query performance, small table against big table; controlling the query optimizer.

8.9. Buffering and Caching: InnoDB buffer pool; key cache access; how the query cache operates; queries must be the same; cache queries options; configuration of query cache; set the query_cache_size;

8.10. Optimizing Locking Operations: Internal and external locking; advantages and disadvantage of table/row locking; when in locking, how the performance will be affected; external locking will also cause issues.

8.11. Optimizing the MySQL Server: System tuning for MySQL; requirements for the system are important; explaining how to optimize the disk drive to provide better performance; more memory is better; thread pool plugin; how thread works and how to fine tune it.

8.12. Measuring Performance (Benchmarking): Use BENECHMARK function to measure the speed; Benchmark suite- is supposed to tell users the performance rating; to find out where is the bottleneck; thread command values; replication of different thread states.

8.13. Internal Details of MySQL Optimizations: Single and multiple parts indexes; merge optimization; use indexes and ranges; left and right join; it can work faster by joining them as there are lesser tables to check; nested loop; explains how the outer join operates; different optimization as ORDER BY, GROUP BY and DISTINCT; using the EXISITS strategy to make things much more simpler.

9. Language Structure

9.1. Literal Values: Enclose a string of characters or bytes within single quotes or double quotes; about ASCII 26; how to quote characters within a string; floating point for integers; date and time can be represented differently; hexadecimals; boolean; NULL value as to represent no data.

9.2. Schema Object Names: Maximum length of different identifiers; identify qualifiers; case sensitivity; how server recognize the names of the function.

9.3 Reserved Words: Reserved words; some words are not allowed such as SELECT as this word has special meaning.

9.4. User-Defined Variables: User can define a variable and pass value into the variable, so it can be used in other statement.

9.5. Expression Syntax: define expression syntax in MySQL; Operators like OR, AND and XOR.

9.6. Comment Syntax: This section shows how to leave comments within the SQL code.

10. Globalization

10.1. Character Set Support: Case-insensitive; select character set and server collation at the start of the setup; different applications may require different character set; error messages; functions to convert data between different character set; unicode support; metadata requirements; use ALTER_TABLE to convert binary or nonbinary string.

10.2. Setting the Error Message Language: Can be changed to other languages instead of the default English.

10.3. Adding a Character Set: Adding character set to MySQL; arrays; support for complex character sets.

10.4. Adding a Collation to a Character Set: How the strings are compared and sorted; collations for 8 bit and non-unicode; Unique ID must be chosen.

10.5. Character Set Configuration: It can be changed when during the startup of server; incorrect path will lead into problems; dynamic character set.

10.6. MySQL Server Time Zone Support: It will detect the time zone when the server start.

10.7. MySQL Server Locale Support: locale support controls the language to be displayed, format of day, month and year.

11. Data Types

11.1. Data Type Overview: Different data types across numeric, data and time, and string.

11.2. Numeric Types: Exact numeric data types; table to show min-max value can be stored; how decimal types are represented in SQL; floating point; approximate values; bit value can store 1-64; out of range and overflow; how MySQL will respond.

11.3. Date and Time Types: MySQL recognize it in several format; explains how they are recognized; limitation for representing year; 2-digits vs 4 digits for representing year.

11.4. String Types: CHAR and VARCHAR similarities and differences; BINARY and VARBINARY; BLOB is a binary large object; treated as binary strings; TEXT treated as nonbinary strings; ENUM advantages; how SET types are been used.

11.7. Choosing the Right Type for a Column: determine which case best suits the situation; for arithmetic calculation, DECIMAL type is best selection but if accuracy is not important, then DOUBLE is more suitable.

11.8. Using Data Types from Other Database Engines: Show a table of different data types represented in other database engines which equivalent to MySQL.

12. Functions and Operators

12.1. Function and Operator Reference: Show a table of different functions/operators and their respective description.

12.3. Operators: Table of operators and their description; highest precedence to lowest; comparison functions and operators; logical and assignment operators.

12.4. Control Flow Functions: Similar to Java IF…ELSE function; comparing the first version result, if it's true it will continue, if not true it will go in the ELSE part.

12.5. String Functions: String comparison functions such as LIKE, NOT LIKE and STRCMP(); regular expressions such as NOT REGEXP, REGEXP and RLIKE.

12.6. Numeric Functions and Operators: Consists of all arithmetic operators and mathematical functions.

12.7. Date and Time Functions: Describes how to specific values for DATE and TIME in the correct format; how to use functions to get what we want.

12.8. What Calendar Is Used By MySQL?: Gregorian.

12.9. Full-Text Search Functions: Full text functions; how is it performed; natural language and Boolean full text search; query expansion; useful when a search phrase is too short; list of full text stopwords; restrictions of full text; fine-tuning full text searches to increase effectiveness.

12.10. Cast Functions and Operators: Explanation and description of how the three different cast functions and operators are been used; BINARY, CAST() and CONVERT().

12.11. XML Functions: Two XML functions, ExtractValue() and UpdateXML(); strong checking, weak checking; XPath limitations.

12.12. Bit Functions: Descriptions of how it perform; BIT_COUNT(), &, ~, |, ^, <<, >>.

12.13. Encryption and Compression Functions: Different encrypt and decrypt functions; how each functions is explained.

12.14. Information Functions: Different functions and the explanations; BENCHMARK(), CHARSET(), COERCIBILITY(), COLLATION(),CONNECTION_ID(),CURRENT_USER(), CURRENT_USER, DATABASE(), FOUND_ROWS(), LAST_INSERT_ID(), ROW_COUNT(), SCHEMA(), SESSION_USER(), SYSTEM_USER(), USER(), VERSION().

12.15. Miscellaneous Functions: Different functions and the explanations, DEFAULT(), GET_LOCK(), INET_ATON(), INET_NTOA(), IS_FREE_LOCK(), IS_USED_LOCK(), MASTER_POS_WAIT(), NAME_CONST(), RAND(), RELEASE_LOCK(), SLEEP(), UUID_SHORT(), UUID(), VALUES().

12.16. Functions and Modifiers for Use with GROUP BY Clauses: Different functions and the explanations; AVG(), BIT_AND(), BIT_OR(), BIT_XOR(), COUNT(DISTINCT) COUNT(), GROUP_CONCAT(), MAX(), MIN(), STD(), STDDEV_POP(), STDDEV_SAMP(), STDDEV(), SUM(), VAR_POP(), VAR_SAMP(), VARIANCE(); GROUP BY modifiers; ROLLUP modifiers; considerations when using ROLLUP.

12.17. Spatial Extensions: To enable generation, storage, and analysis of geographic features; geographic features, a city, town district, a crossroad; geometry class hierarchy; Geometry, Point, Curve, LineString, Surface, Polygon, GeometryCollection, MultiPoint, MultiCurve, MultiLineString, MultiSurface, MultiPolygon; Well-Known Text (WKT); designed to exchange geometry data in ASCII form; Well-Known Binary (WKB); defined by the OpenGIS specification; spatial data types; how to create spatial values using WKT and WKB.

12.18. Precision Math: Features for numeric operations; exact values are used as given; rounding up of values; examples of precision math.

13. SQL Statement Syntax

13.1. Data Definition Statements: 10 syntaxes are explained; ALTER, CREATE and DROP; ALTER, CREATE and DROP DATABASE - change the overall characteristics of a database, creates databases with given name, drops all tables and delete databases; ALTER, CREATE and DROP EVENT - changes one or more characteristics of existing event without dropping it, creates and schedule new event, drops the event; ALTER, CREATE and DROP LOGFILE GROUP - is to adds an UNDO file to existing log file, creates a new log file group, drops log file group; ALTER, CREATE and DROP FUNCTION - change the characteristics of stored function, create functions and user-defined functions, drop stored functions; ALTER, CREATE and DROP PROCEDURE - change characteristics of stored procedure, creates new procedures, drop procedures; ALTER, CREATE and DROP SERVER - change server information, creates the definition of server, drop server definition; ALTER, CREATE and DROP TABLE - change structure of a table, creates table with given name, drop selected table; ALTER, CREATE and DROP TABLESPACE - add or drop data file, creates and drop; ALTER, CREATE and DROP VIEW - changes the definition of a view, creates and drop; CREATE and DROP Trigger - which created to trigger particular event, drop trigger; RENAME TABLE - renames one or more tables; TRUNCATE TABLE - empties table completely.

13.2. Data Manipulation Statements: CALL - invokes a stored procedure what was previously defined, can pass back values to its caller; DELETE - deletes rows from table, supports modifiers; DO - does not return any results, is a shorthand for SELECT, is slightly faster. HANDLER - provides direct access to table storage; INSERT - puts in new row; LOAD DATA INFILE/ LOAD XML - read row from text file in high speed, read data from an XML file into a table; REPLACE - same as INSERT but if same value, it will delete the old row; SELECT - retrieve rows selected from one or more tables; UPDATE - updates columns of values in existing rows.

13.3. MySQL Transactional and Locking Statements: START TRANSACTION, COMMIT and ROLLBACK provide control over use of transactions; DDL statements cannot be rolled back; statements that cause implicit commit; lock and unlock table.

13.4. Replication Statements: Statements controlling the master and slave server.

13.5. SQL Syntax for Prepared Statements: PREPARE/ EXECUTE syntax; prepares a statement and assign a name; execute the prepared statement; DEALLOCATE PREPARE syntax.

13.6. MySQL Compound-Statement Syntax: BEGIN… END syntax is used for compound statements; compound statement can contain multiple statements enclosed by the keywords; system and user-defined variables can be used in stored programs; description of 8 flow control statements; 4 cursors syntax, CLOSE, DECLARE, FETCH, OPEN; condition handling as issue may arise.

13.7. Database Administration Statements: 6 syntax to manage account in database; CREATE USER, DROP USER, GRANT, RENAME USER, REVOKE and SET PASSWORD; 5 syntax to do maintaining of table in database; ANALYZE TABLE, CHECK TABLE, CHECKSUM TABLE, OPTIMIZE TABLE, REPAIR TABLE; able to create functions defined by users.

13.8. MySQL Utility Statements: 4 syntax; DESCRIBE, EXPLAIN, HELP and USE;

14. Storage Engines

14.2. Overview of MySQL Storage Engine Architecture: INSTALL and UNINSTALL PLUGIN;

14.3. The InnoDB Storage Engine: Benefits of using InnoDB; configuring; tablespaces; startup options; creating and using InnoDB tables; Manipulation of InnoDB, adding, removing, backing up, recovering, moving database to another machine; InnoDB table and index structures; error handling, troubleshooting; the basic stuffs of working with InnoDB.

14.5. The MyISAM Storage Engine: How MyISAM is going to work; space needed for keys; use B-tree indexes; 3 different table storage formats; issue with MyISAM.

14.6. The MEMORY Storage Engine: Known as HEAP; how/ where is it used.

14.7. The CSV Storage Engine: How to repairing/ checking is done; limitations- partitioning.

14.8. The ARCHIVE Storage Engine: Used for storing large amount of data.

14.9. The BLACKHOLE Storage Engine: Accept data but do not store it.

14.10. The MERGE Storage Engine: Advantages and Disadvantages of using MERGE table.

14.11. The FEDERATED Storage Engine: How to create the table, tips and notes of using it.

15. High Availability and Scalability

15.3. Using MySQL within an Amazon EC2 Instance: How EC2 AMI is been setup and deployed; there are limitations as well.

15.4. Using ZFS Replication: Configuring and handling the ZFS; recovery with ZFS; how to use ZFS.

15.5. Using MySQL with memcached: Description of how to use the memcached; installing it; developing application.

15.6. MySQL Proxy: Installing and using the MySQL proxy; supported platforms; command options that run it directly from command line; proxy scripting.

16. Replication

16.1. Replication Configuration: Explaining how to setup; Advantages and Disadvantages of using the formats; Master and slave options; checking of status.

16.2. Replication Implementation: One master and two slaves; relay and status logs for master and slaves.

16.3. Replication Solutions: Use for backups - mysqldump; different storage engines; scale-out - spread the load of database queries across databases; how to improve the performance; SSL.

17. MySQL Cluster NDB 7.2

17.1. MySQL Cluster Overview: Concepts of NDBCLUSTER; some terms which MySQL cluster divide and duplicate data - Nodes, Node Groups, Replicas and Partitions; limitations of MySQL cluster - error handling, unsupported features; limitations exclusive to MySQL cluster.

17.3. MySQL Cluster Configuration: Configuration parameters; the files that needed to configure the MySQL cluster; different server options; using of SCI sockets; 4 access methods.

17.4. MySQL Cluster Programs: Basics like selecting all rows and columns, configure information for data nodes, drop table/index, restore backup, and display tables.

17.5. Management of MySQL Cluster: The different startup types and modes; how to create backup using MySQL cluster management, configuring it and as well troubleshooting it; rolling restart of the cluster; single mode - restricted access; privilege system and network security issues.

17.6. MySQL Cluster Replication: Symbols and abbreviations such as 'M' for Master, 'S' for Slave; the problems of using replication with MySQL cluster; single and two channel cluster replication; conflict resolution when multiple masters involved; circular replication.

18. Partitioning

18.2. Partitioning Types: 5 different types and 1 subpartitioning; RANGE, LIST, COLUMNS, HASH and KEY; composite partitioning.

18.3. Partition Management: Management of the different type of partitions; RANGE and LIST are very similar; how to obtain the information from different partitions.

18.4. Partition Pruning: Concept behind pruning is not to scan the partitions where there are no matching values.

18.5. Restrictions and Limitations on Partitioning: About primary and unique key; if table no primary or unique key then restriction does not apply; limitations relating to storage engines and functions.

19. Stored Programs and Views

19.2. Using Stored Routines (Procedures and Functions): Stored routines are created with CREATE PROCEDURE; recursions are disabled at default.

19.3. Using Triggers: Syntax that is used to create and drop statements; MySQL handle errors during trigger execution; can obtain metadata.

19.4. Using the Event Scheduler: Configuration for event scheduler; OFF/ON functions; metadata for event; information can be obtained by running mysqladmin debug; diagnostics messages are written to the error log.

19.5. Using Views: How to use VIEW syntax to create new view; processing algorithms; some views can be updates and be inserted; there will be situation too that a view is not updateable.

19.6. Access Control for Stored Programs and Views: MySQL have rules to control the account and user; DEFINER attribute - view and execute stored programs; stored routines have security.

19.7. Binary Logging of Stored Programs: contains information about SQL statements that modify database contents; important purposes - replication and data recovery.

20. INFORMATION_SCHEMA Tables: It basically provides access to the metadata; other terms - data dictionary and system catalog; it has all the information about other databases; it contains only read-only table, only available for viewing and not editing the table; there are other functions explained under this particular topic; like CHARACTER_SETS, TABLE, TABLESPACE etc; explanations is there for each of the functions; INFORMATION_SCHEMA for InnoDB and MySQL cluster; extensions to the SHOW statements.

21. MySQL Performance Schema

21.2. Performance Schema Configuration: 3 configurations - build, startup and runtime.

21.4. Performance Schema Instrument Naming Conventions: instrument names are separated by '/'; naming from left to right provide a progression from more general to more specific; like windows file manager system.

21.5. Performance Schema Status Monitoring: provide information about instrumentation that not able to load or create due to constraints.

21.7. Performance Schema Table Descriptions: 5 tables in performance_schema database; setup tables - configure and display characteristics; instance table - document what types of object are instrumented; wait event table - table that store wait events; summary table - contains groups of events including those that are discarded; miscellaneous table - timers and threads;

21.11. Using the Performance Schema to Diagnose Problems: A tool to help troubleshooting; examples of how to analyze repeatable problem.

22. Connectors and APIs: How the connectors and API work together and let user to execute SQL statements from another language and environment; it can be done in JAVA, ODBD, PERL, PYTHON, PHP, RUBY, NATIVE C and OPENOFFICE.org.

23. Extending MySQL

23.1. MySQL Internals: Threads - MySQL server creates different threads for different purposes; test suite in MySQL to perform regression tests.

23.2. The MySQL Plugin API: Plugin API characteristics and components; plugins have several things in common, information can be exposed to users, INSTALL PLUGIN and UNINSTALL PLUGIN; different types of plugins; write your own plugins, compiling and installing libraries.

23.4. Debugging and Porting MySQL: Configure MySQL to debug with facing issue; debug package are in default; creating trace files and using stack trace; using error logs to find the cause;

26. MySQL for Excel: Installing and configuring MySQL to work with Excel; how to edit, import and append data in Excel; lastly is how to export MySQL data to MySQL database.

A (3)

Data Description

1. Country table

Field

Type

Null

Key

Default

Extra

Name

varchar(35)

NO

UNI

NULL

Code

varchar(4)

NO

PRI

Capital

varchar(35)

YES

NULL

Province

varchar(35)

YES

NULL

Area

float

YES

NULL

Population

int(11)

YES

NULL

Country table has 238 rows.

2. City table

Field

Type

Null

Key

Default

Extra

Name

varchar(35)

NO

PRI

Country

varchar(4)

NO

PRI

Province

varchar(35)

NO

PRI

Population

int(11)

YES

NULL

Longitude

float

YES

NULL

Latitude

float

YES

NULL

City table has 3111 rows.

3. Province table

Field

Type

Null

Key

Default

Extra

Name

varchar(35)

NO

PRI

NULL

Country

varchar(4)

NO

PRI

NULL

Population

int(11)

YES

NULL

Area

float

YES

NULL

Capital

varchar(35)

YES

NULL

CapProv

varchar(35)

YES

NULL

Province table has 1450 rows.

4. Economy table

Field

Type

Null

Key

Default

Extra

Country

varchar(4)

NO

PRI

GDP

float

YES

NULL

Agriculture

float

YES

NULL

Service

float

YES

NULL

Industry

float

YES

NULL

Inflation

float

YES

NULL

Economy table has 238 rows.

5. Population table

Field

Type

Null

Key

Default

Extra

Country

varchar(4)

NO

PRI

Population_Growth

float

YES

NULL

Infant_Mortality

float

YES

NULL

Population table has 238 rows.

6. Politics table

Field

Type

Null

Key

Default

Extra

Country

varchar(4)

NO

PRI

Independence

date

YES

NULL

Dependent

varchar(4)

YES

NULL

Government

varchar(120)

YES

NULL

Politics table has 238 rows.

7. Language table

Field

Type

Null

Key

Default

Extra

Country

varchar(4)

NO

PRI

Name

varchar(50)

NO

PRI

Percentage

float

YES

NULL

Language table has 144 rows.

8. Religion table

Field

Type

Null

Key

Default

Extra

Country

varchar(4)

NO

PRI

Name

varchar(50)

NO

PRI

Percentage

float

YES

NULL

Religion table has 454 rows.

9. EthnicGroup table

Field

Type

Null

Key

Default

Extra

Country

varchar(4)

NO

PRI

Name

varchar(50)

NO

PRI

Percentage

float

YES

NULL

EthnicGroup table has 540 rows.

10. Continent table

Field

Type

Null

Key

Default

Extra

Name

varchar(20)

NO

PRI

Area

float

YES

NULL

Continent table has 5 rows.

11. borders table

Field

Type

Null

Key

Default

Extra

Country1

varchar(4)

NO

PRI

Country2

varchar(4)

NO

PRI

Length

float

YES

NULL

borders table has 320 rows.

12. encompasses table

Field

Type

Null

Key

Default

Extra

Country

varchar(4)

NO

PRI

Continent

varchar(20)

NO

PRI

Percentage

float

YES

NULL

Encompasses table has 242 rows.

13. Organization table

Field

Type

Null

Key

Default

Extra

Abbreviation

varchar(12)

NO

PRI

NULL

Name

varchar(80)

NO

UNI

NULL

City

varchar(35)

YES

NULL

Country

varchar(4)

YES

NULL

Province

varchar(35)

YES

NULL

Established

date

YES

NULL

Organization table has 153 rows.

14. isMember table

Field

Type

Null

Key

Default

Extra

Country

varchar(4)

NO

PRI

Organization

varchar(12)

NO

PRI

Type

varchar(35)

YES

member

isMember table has 8008 rows.

15. Mountain table

Field

Type

Null

Key

Default

Extra

Name

varchar(35)

NO

PRI

Mountains

varchar(35)

YES

NULL

Height

float

YES

NULL

Type

varchar(10)

YES

NULL

Longitude

float

YES

NULL

Latitude

float

YES

NULL

Mountain table has 240 rows.

16. Desert table

Field

Type

Null

Key

Default

Extra

Name

varchar(35)

NO

PRI

Area

float

YES

NULL

Longitude

float

YES

NULL

Latitude

float

YES

NULL

Desert table has 63 rows.

17. Island table

Field

Type

Null

Key

Default

Extra

Name

varchar(35)

NO

PRI

Islands

varchar(35)

YES

NULL

Area

float

YES

NULL

Height

float

YES

NULL

Type

varchar(10)

YES

NULL

Longitude

float

YES

NULL

Latitude

float

YES

NULL

Island table has 276 rows.

18. Lake table

Field

Type

Null

Key

Default

Extra

Name

varchar(35)

NO

PRI

Area

float

YES

NULL

Depth

float

YES

NULL

Altitude

float

YES

NULL

Type

varchar(10)

YES

NULL

River

varchar(35)

YES

NULL

Longitude

float

YES

NULL

Latitude

float

YES

NULL

Lake table has 130 rows.

19. Sea table

Field

Type

Null

Key

Default

Extra

Name

varchar(35)

NO

PRI

Depth

float

YES

NULL

Sea table has 34 rows.

20. River table

Field

Type

Null

Key

Default

Extra

Name

varchar(35)

NO

PRI

River

varchar(35)

YES

NULL

Lake

varchar(35)

YES

NULL

Sea

varchar(35)

YES

NULL

Length

float

YES

NULL

SourceLongitude

float

YES

NULL

SourceLatitude

float

YES

NULL

Mountains

varchar(35)

YES

NULL

SourceAltitude

float

YES

NULL

EstuaryLongitude

float

YES

NULL

EstuaryLatitude

float

YES

NULL

River table has 218 rows.

21. geo_Mountain table

Field

Type

Null

Key

Default

Extra

Mountain

varchar(35)

NO

PRI

Country

varchar(4)

NO

PRI

Province

varchar(35)

NO

PRI

geo_Mountain table has 295 rows.

22. geo_Desert table

Field

Type

Null

Key

Default

Extra

Desert

varchar(35)

NO

PRI

Country

varchar(4)

NO

PRI

Province

varchar(35)

NO

PRI

geo_Desert table has 154 rows.

23. geo_Island table

Field

Type

Null

Key

Default

Extra

Island

varchar(35)

NO

PRI

Country

varchar(4)

NO

PRI

Province

varchar(35)

NO

PRI

geo_Island table has 418 rows.

24. geo_River table

Field

Type

Null

Key

Default

Extra

River

varchar(35)

NO

PRI

Country

varchar(4)

NO

PRI

Province

varchar(35)

NO

PRI

geo_River table has 851 rows.

25. geo_Sea table

Field

Type

Null

Key

Default

Extra

Sea

varchar(35)

NO

PRI

Country

varchar(4)

NO

PRI

Province

varchar(35)

NO

PRI

geo_Sea table has 735 rows.

26. geo_Lake table

Field

Type

Null

Key

Default

Extra

Lake

varchar(35)

NO

PRI

Country

varchar(4)

NO

PRI

Province

varchar(35)

NO

PRI

geo_Lake table has 253 rows.

27. geo_Source table

Field

Type

Null

Key

Default

Extra

River

varchar(35)

NO

PRI

Country

varchar(4)

NO

PRI

Province

varchar(35)

NO

PRI

geo_Source table has 219 rows.

28. geo_Estuary table

Field

Type

Null

Key

Default

Extra

River

varchar(35)

NO

PRI

Country

varchar(4)

NO

PRI

Province

varchar(35)

NO

PRI

geo_Estuary table has 265 rows.

29. mergesWith table

Field

Type

Null

Key

Default

Extra

Sea1

varchar(35)

NO

PRI

Sea2

varchar(35)

NO

PRI

mergesWith table has 54 rows.

30. located table

Field

Type

Null

Key

Default

Extra

City

varchar(35)

YES

NULL

Province

varchar(35)

YES

NULL

Country

varchar(4)

YES

NULL

River

varchar(35)

YES

NULL

Lake

varchar(35)

YES

NULL

Sea

varchar(35)

YES

NULL

located table has 857 rows.

31. locatedOn table

Field

Type

Null

Key

Default

Extra

City

varchar(35)

NO

PRI

Province

varchar(35)

NO

PRI

Country

varchar(4)

NO

PRI

Island

varchar(35)

NO

PRI

locatedOn table has 434 rows.

32. islandIn table

Field

Type

Null

Key

Default

Extra

Island

varchar(35)

YES

NULL

Sea

varchar(35)

YES

NULL

Lake

varchar(35)

YES

NULL

River

varchar(35)

YES

NULL

islandIn table has 349 rows.

33. MountainOnIsland table

Field

Type

Null

Key

Default

Extra

Mountain

varchar(35)

NO

PRI

Island

varchar(35)

NO

PRI

MountainOnIsland table has 67 rows.

PART B

B (1)

Database store information neatly according in field such as Student List, Student Name, Age and so on. It has structures and contents like in example A and C, not like example B is too general and unstructured.

Compared to text search engine in the online textbook, database can do complex searching to query the search word in all applicable fields. Unlike the text search engine, though it can used for article searching but if we need more precise results the database can give a more narrow down results.

Full text search can perform faster searching in unstructured text for any word or combination of words. On the other hand, DBMS are better at storing and handling structured data. It also provides a better environment of updating records safely.

B (2)

No, as the job of data entry clerk does not require any technical knowledge of how the database works. Their job is just plainly adding data into the database.

About 27,711.

Because of human error, the mission to mars is a failure as mistranslated code.

In the article, it mentioned that when a user is more skilled, the less attention they will pay to what should be the outcome, so more error will go unnoticed. Skilled user usually tends to be selectively in their attention, so they pay less attention to the display.

It's about 71% - 98%

B (3)

8(rows) x 8(fields) = 64 characters/digits

64 characters/ digits x 500,000(rows) = 32,000,000 characters/digits

32,000,000 x 10(tables) = 320,000,000 characters/digits

Klemmer [1962] error rate is 0.02% - 0.06%

Therefore,

(0.04/100) x 320,000,000 = 128 000 characters/digits

Baddeley & Longman [1973] error rate is 0.5%

Therefore,

(0.5/100) x 320,000,000 = 1,600,000 characters/digits

B (4)

Digit

4

6

5

7

4

6

8

6

6

9

8

7

0

7

6

0

Weight

2

1

2

1

2

1

2

1

2

1

2

1

2

1

2

1

"Product"

8

6

1

7

8

6

7

6

3

9

7

7

0

7

3

0

Sum of "Product" = 85

Digit

4

6

5

8

4

6

8

6

6

9

8

7

0

7

6

0

Weight

2

1

2

1

2

1

2

1

2

1

2

1

2

1

2

1

"Product"

8

6

1

8

8

6

7

6

3

9

7

7

0

7

3

0

Sum of "Product" = 86

Digit

4

6

5

9

4

9

9

8

6

9

8

8

0

7

6

0

Weight

2

1

2

1

2

1

2

1

2

1

2

1

2

1

2

1

"Product"

8

6

1

9

8

9

9

8

3

9

7

8

0

7

3

0

Sum of "Product" = 95

All the above credit card numbers are not valid.

B (5)

Constraint with primary key.

Constraint with checking values.

No.

B (6)

To: Tan Ah Kow, IT Manager

From: Kong Mun Wong

Date: 3 December 2012

Subject: Database Implementation

Dear Mr. Tan,

I am writing to you as to tell you that spending more time for implementing data integrity constraints are worthwhile and it is actually an important aspect that ought not to be missed out.

The importance of data integrity constraints is crucial. Is to ensure data integrity for data that are going to be entered into the database is correct and not duplicated. It also helps to protect the data that relies on or depends on exist and does not get deleted. There are a few methods that can prevent that. In an example of a column can only contain values 'T' or 'F', the Check Constraints can be used to check whether the value entered in the specific column is either 'T' or 'F'.

By giving necessary training to data entry operators and using Optical Character Recognition (OCR) does not mean that human errors will be totally eliminated. Research have shown that skilled data entry operators are more easily to have human errors as they tend to be selective in checking, therefore more errors will go unnoticed.

Though the OCR gives lesser error rates but it will have some human errors too. Scanning 10~50 pages is definitely all right for the operators, but when the load increased to maybe 200~500 pages and there is hundreds of books waiting to be scanned. The operators will most likely to miss out a few pages and that are inevitable. Researches have shown although OCR should read 98% of words correctly but some words will still be unable to read especially words with pencil/pen marks over it and original documents such as faxes or hand-written documents. Therefore, OCR success rate will drops significantly.

Therefore, I hereby to request that you are able to give me a longer period of time (2 weeks) to let me have a proper planning and implementation of the new database so that erroneous data can be minimized.

Yours Faithfully,

Kong Mun Wong

B (7)

Groom =>> Horses (a groom can take several horses)

Horse => Groom (a horse can only have one groom)

Groom =>> Horses (a groom can take several horses)

Horse =>> Grooms (a horse can be "shared" by two grooms)

Groom => Horse (a groom can take only one horse)

Horse => Groom (a horse can only have one groom)

Horse =>> Grooms (a horse can have several grooms)

B (8)

In this situation, Aphra's proposal is the correct decision. Primary key has to be unique and Student Number was given by the college so therefore it is special.

In Betty's proposal, making Student Number + Subject as primary is not feasible as well because this particular student 'X' retake 'CIS001' for the year 2012 but he failed and therefore he resits again the following year. The records will be overwritten by the latest results and not keeping the results he had in year 2012.

In Gemma and Dot's proposal, making Student Number + Subject + Date or Student Number + Subject + Date + Grade as primary keys is not appropriate as well because we will not know what the student will get for their grade in their resits. Therefore, this leads to unknown value (NULL) in "Grade" column which is not allowed if Grade is one of the primary key. Likewise for the date of the exam, the school will only have the date at a much later time so it will have unknown value as well.

Lastly, it only can have one primary key in a table.

B (9)

E/R Diagram

Coursename

Surname

StudentNo

Coursecode

has

Students

takes

Courses

Teachers

stays

Birthdate

Firstname

Middlename

EmployeeNo

Rooms

RoomNo

Middlename

Surname

Phone

Extension

Firstname

B (10)

Massachusetts General Hospital Utility Multi-Programming System (MUMPS) was originally used in medical records but now it also widely used. The unique features of MUMPS is having a "built-in" database, having high-level access to disk storage, therefore allows multiple users to access the database simultaneously. There are still industries using MUMPS till today, such as from banking sector, Bank of England and Partners HealthCare from the healthcare industry.

MUMPS is languages that are intended to design database applications and also to help programmers to make applications using limited resources from the early days as computer are not that developed. MUMPS programs are small, it can fit multiple programs into small memory partition and also able to provide multi-user environment. Multi-Programming is the language that makes MUMPS running machines to be able to support multiple jobs running at the same time.

MUMPS language design is also different as memory is limited, therefore the commands and functions name are short. Though it is easy in some way as abbreviation is simple, but in early days, MUMPS programmers did not leave comment code and used of abbreviation are extensively used so the programmers have to analyze the code line by line instead of skimming through.

NoSQL database can also be referred as Not Only SQL database. No standard definition for it but basically is a database management system that do not adhere to Relational data model. Some characteristics are NoSQL database are not built mainly on tables and is designed to run on a cluster. They do not use the SQL for data manipulation.

NoSQL databases are good for industries that require retrieve and append huge quantity of data. Such as Google as their search engine are the largest, there are many unique URLs need to be stored and the amount of data will only increase and not the otherwise. The other two major companies are Facebook and Amazon.com as well. Big data basically is the data that is accumulated from everywhere including digital picture, climate information and cell phone GPS etc.

Industries now can use this big data to recalculate their risk management, identify customers, causes of failures and many more.

NoSQL have four categories that make it an advantage over the rigid relational database,

Key-value stores - it means mapping a key to a value, so it is much faster.

Column-Oriented Stores - it means the key stores all data in the column.

Document Databases - same as key-value store, but documents are made up of named fields. Compared putting data into a table, document databases is more "readable".

Graph databases - is a multi-relational graph that is more flexible than tables and rows in SQL.

When there are advantages, disadvantages will come along too. NoSQL is not as mature as relational database management system (RDBMS) because RDBMS have been around for a long time; therefore the stability for RDBMS is definitely better. Since the NoSQL is open source projects, the support is definitely weaker than RDBMS. Lastly, it also takes a lot skill to install and maintain NoSQL database.

B (11)

Data Index is a structure that improves the performance of data retrieval. 'Index' in database management system context means is a list of data that are saved in one part of a table and can be quickly scanned by a search algorithm. This can speeds up the searching operation. It is quite the same as index of a book makes finding a particular topic easier. Instead of browsing through the whole book, we can look at the book's index to get what we want to look for.

"Good Indexing May Improve Database Performance" (TechRepublic.com, 2007)

It mentioned that it can improve the performance of the database by having good indexing. So what "performance" it meant? Basically is the search time that needed for. As mentioned earlier on, with indexing, the searching will be much faster than searching the whole database. It also improves the SQL statements especially in larger tables. Can it be the "physical performance" as well? What exactly physical is actually are the hardware components that used in the database systems. Having more Random Access Memory (RAM) and bigger capacity of Hard Disk (HDD) will buff up the performance? Definitely having more RAM can speed up the process time as it uses cache so indexing definitely faster but the downside is adding more memory is more expensive. Since indexing is not about the raw data, therefore capacity of HDD is not really an important factor.

In full-table scan, it means that it scan the whole database in a sequential order. Since it needs to scan the whole data, it is the slowest method. Clustered index is storing data close to one another so it can have fast retrieval speeds. Non clustered index data is stored in one place and the index is stored in another. To retrieve data, there are pointers to the storage of data.

Advantages of using indexing:

Indexes make search queries faster.

Indexes like primary key, unique index can prevent repeated row data.

Disadvantages of using indexing:

It will slow operations like INSERT, UPDATE and DELETE as there are more indexes; the tables need longer time to update.

Indexes take up disk spaces, though is not significant but index file can grow much faster than data file.

Writing Services

Essay Writing
Service

Find out how the very best essay writing service can help you accomplish more and achieve higher marks today.

Assignment Writing Service

From complicated assignments to tricky tasks, our experts can tackle virtually any question thrown at them.

Dissertation Writing Service

A dissertation (also known as a thesis or research project) is probably the most important piece of work for any student! From full dissertations to individual chapters, we’re on hand to support you.

Coursework Writing Service

Our expert qualified writers can help you get your coursework right first time, every time.

Dissertation Proposal Service

The first step to completing a dissertation is to create a proposal that talks about what you wish to do. Our experts can design suitable methodologies - perfect to help you get started with a dissertation.

Report Writing
Service

Reports for any audience. Perfectly structured, professionally written, and tailored to suit your exact requirements.

Essay Skeleton Answer Service

If you’re just looking for some help to get started on an essay, our outline service provides you with a perfect essay plan.

Marking & Proofreading Service

Not sure if your work is hitting the mark? Struggling to get feedback from your lecturer? Our premium marking service was created just for you - get the feedback you deserve now.

Exam Revision
Service

Exams can be one of the most stressful experiences you’ll ever have! Revision is key, and we’re here to help. With custom created revision notes and exam answers, you’ll never feel underprepared again.