{"id":8869,"date":"2019-12-15T07:43:48","date_gmt":"2019-12-15T07:43:48","guid":{"rendered":"http:\/\/probook\/DebianAdmin\/?page_id=8382"},"modified":"2025-06-01T08:13:10","modified_gmt":"2025-06-01T07:13:10","slug":"databases-a-research-paper-by-steve-edwards-2009-2","status":"publish","type":"page","link":"https:\/\/stevepedwards.today\/DebianAdmin\/databases-a-research-paper-by-steve-edwards-2009-2\/","title":{"rendered":"Databases \u2013 My Research Paper 2009"},"content":{"rendered":"
<\/div>\n

<\/path><\/svg><\/i> \"Loading\"<\/p>\n

<\/div>\n

Databases \u2013 A Research Paper<\/b><\/u><\/span>\u00a0by\u00a0<\/span>Steve Edwards 2009<\/u><\/i><\/span><\/h1>\n

Introduction<\/h2>\n

As there are multiple ways in which electronic data can be stored, accessed, altered, and defined, so there are many different types of Databases in existence, storing and manipulating data for a particular purpose. Differences between possible configurations of various electronic Multi-User Databases will be investigated - where access to stored data by one or more individuals - seemingly \u201dsimultaneously\" - is possible, particularly in the Centralised, Client\/Server and the Distributed Database models, along with<\/span><\/span>\u00a0<\/b><\/span><\/span>some of their characteristics, advantages and disadvantages.<\/span><\/span><\/span><\/span><\/p>\n

But what is an electronic Database and why is its creation of use?<\/span><\/span><\/span><\/span><\/p>\n

The online Oxford English Dictionary defines a Database thus:<\/span><\/span><\/span><\/span><\/p>\n

Database Definition:<\/span><\/b><\/i><\/span><\/span><\/span>\u00a0<\/i>-<\/i><\/span>database<\/b><\/i><\/span>\u00a0\u2022 noun a structured set of data held in a computer.\u201d<\/i><\/span><\/span><\/span><\/p>\n

Compact Oxford English Dictionary,\u00a0<\/span><\/span>https:\/\/www.askoxford.com\/concise_oed\/database?view=uk<\/span><\/span><\/span><\/a>, p1.p1 Date accessed 6\/11\/2009<\/span><\/span><\/span><\/p>\n

The key words here are \"structured set\u201d as it is this characteristic of the data and how it functions as a structure within a Database model that differentiates it from other electronic data that is just stored collectively as individual, possibly unrelated files on particular electronic media such as hard drives, tape devices etc.<\/span><\/span><\/span><\/span><\/p>\n

A more holistic, non-technical description and purpose of the Database is given below, again intimating that data structuring and inter-relationships between multiple collections of data are involved and innate, and so purposefully arranged to be analogous to something in reality, and give raw data some meaning so that it can be of practical use:<\/span><\/span><\/span><\/span><\/p>\n

-...to model the collective user perceptions of the real world. The database reflects an image of the real world; an image that governs the behaviour of people and application processes in the organization.\u201d<\/i><\/span><\/span><\/span><\/p>\n

Gordon C. Everest (1986)\u00a0<\/span><\/span>Database Management<\/i><\/span><\/span>, Singapore: McGraw-Hill Book Co.<\/span><\/span><\/span><\/span><\/p>\n

To understand more clearly the reasons why structured data is desirable, a distinction between data and information is required as provided by Terence Hanold in 1972, from -An Executive View of MIS\u201d and quoted by Everest:<\/span><\/span><\/span><\/span><\/p>\n

Information Definition:\u00a0<\/span><\/b><\/i><\/span><\/span><\/span>-Data is transformed into information through the infusion of purposeful intelligence. Thus, information is data refined by intelligence so that it communicates meaning or knowledge...\u201d<\/i><\/span><\/span><\/span><\/p>\n

Gordon C. Everest (1986)\u00a0<\/span><\/span>Database Management<\/i><\/span><\/span>, Singapore: McGraw-Hill Book Co.<\/span><\/span><\/span><\/span><\/p>\n

So now there is reason to structure data so that it can be manipulated with a\u00a0<\/span><\/span>purpose<\/i><\/span><\/span>\u00a0to become\u00a0<\/span><\/span>information<\/i><\/span><\/span>\u00a0and so\u00a0<\/span><\/span>useful,\u00a0<\/i><\/span><\/span>so hopefully there is a clearer foundation for the Layperson to continue with.<\/span><\/span><\/span><\/span><\/p>\n

The precise origins and first use of the term \"database\u201d are unclear, but one possibility is that in 1964 the term derived from the joining of the words -data\u201d and -base\u201d to reflect the notion of an original data source file or \"Master\u201d file being the \"base\u201d to which other data from other sources can be augmented, as referenced from an original study of the involvement of IBM computers in the historical development of Databases, by <\/span><\/span>W.\u00a0C.\u00a0McGee<\/span><\/span>.<\/span><\/span><\/span><\/span><\/p>\n

-<\/span>Around\u00a0<\/i><\/span>1964\u00a0<\/b><\/i><\/span>a new term appeared in the computer<\/i><\/span><\/span><\/span><\/p>\n

literature to denote a new concept. The term was -data<\/i><\/span><\/span><\/span><\/p>\n

base,\u201d and it was coined by workers in military information<\/i><\/span><\/span><\/span><\/p>\n

systems to denote collections of data shared by end users<\/i><\/span><\/span><\/span><\/p>\n

of time-sharing computer systems\u201d...<\/i><\/span>\u00a0-<\/i><\/span><\/span>As a result, common data items<\/span><\/span><\/span><\/p>\n

often appeared in different master files, and the values of<\/span><\/span><\/span><\/p>\n

such items often did not agree. There was thus a requirement<\/span><\/span><\/span><\/p>\n

to consolidate the various master files into a single<\/span><\/span><\/span><\/p>\n

data base which could be centrally maintained\u201d<\/span><\/span><\/span><\/p>\n

WC McGee (1981)\u00a0<\/span><\/span>Data Base Technology,<\/i><\/span><\/span>\u00a0PDF\u00a0<\/span><\/span>https:\/\/domino.watson.ibm.com\/tchjr\/journalindex.nsf\/c469af92ea9eceac85256bd50048567c\/18c2b2dadee8a44985256bfa0067f4d8?OpenDocument<\/span><\/span><\/span><\/a>\u00a0p1,p505\u00a0<\/span><\/span>IBM I.\u00a0<\/b><\/span><\/span>RES. DEVELOP. VOL.\u00a0<\/span><\/span>25 0\u00a0<\/b><\/i><\/span><\/span>NO.\u00a0<\/b><\/span><\/span><\/b><\/i>SEFTEMBER\u00a0<\/span><\/span>1981<\/b><\/span><\/span><\/span><\/span><\/p>\n

1.\u00a0\u00a0\u00a0\u00a0 Discuss the differences between centralised, client server and distributed database configuration.\u00a0 Explain where it would be appropriate to use these configurations.<\/i><\/span><\/h1>\n

To get a foundation from which to proceed, external referenced definitions for each configuration will be used.<\/span><\/span><\/span><\/span><\/p>\n

Centralised Database Configuration:<\/h2>\n

To Centralize Definition:<\/span><\/span><\/b><\/i><\/span><\/span><\/span>\u00a0<\/span><\/span><\/b><\/i><\/span><\/span><\/span>-<\/i><\/span><\/span><\/span>centralize<\/i><\/span><\/span><\/span>\u00a0<\/span>\u2022 verb concentrate (control or power) under a single authority\u201d<\/i><\/span><\/span><\/span><\/h2>\n

Compact Oxford English Dictionary,\u00a0<\/span><\/span>https:\/\/www.askoxford.com\/concise_oed\/database?view=uk<\/span><\/span><\/span><\/a>, p1.p1 Date accessed 9\/11\/2009<\/span><\/span><\/span><\/p>\n

Distributed Database Configuration:<\/h2>\n

Distributed Definition:<\/span><\/span><\/b><\/i><\/span><\/span><\/span>\u00a0<\/span><\/span><\/b><\/i><\/span><\/span><\/span>-A logically interrelated collection of shared data (and a description of this data physically distributed over a computer network.\u201d<\/i><\/span><\/span><\/h2>\n

Thomas Connolly, Carolyn Begg, Anne Strachan (1995) Database Systems 2<\/span><\/span>nd<\/span><\/span><\/sup>\u00a0ED: Pearson Education<\/span><\/span><\/span><\/span><\/p>\n

Client\/Server Configuration*:<\/h2>\n

Client\/Server Definition:<\/span><\/span><\/b><\/i><\/span><\/span><\/span>\u00a0<\/span><\/span><\/b><\/i><\/span><\/span><\/span>-In the client\/server system, the main computer, called a server, is accessible from a network \u2013 typically a local area network (LAN) or a wide area network (WAN)\u201d<\/i><\/span><\/span><\/h2>\n

Andrew S. Tanenbaum (2003)\u00a0<\/span><\/span>Computer Networks 4<\/i><\/span><\/span>th<\/i><\/span><\/span><\/sup>\u00a0ED,\u00a0<\/i><\/span><\/span>Upper Saddle River, NJ: Pearson Education<\/span><\/span><\/span><\/span><\/p>\n

*Note that this is NOT a Database\u00a0<\/i><\/span><\/span>type<\/b><\/i><\/span><\/span>\u00a0definition but a data transfer process that is a fundamental part of most network Distributed AND Centralised models, to enable access to the data held on the Database Server by a user (the Client).<\/i><\/span><\/span><\/span><\/span><\/p>\n

Centralised and Distributed Databases<\/h2>\n

We can infer from the prior insert by McGee, that Centralisation was an original desire in early database designs for Administration and data protection purposes but also, networked systems, certainly in the sense that we know them today, did not exist in the 1960s, or the mathematical theory of Relational Data, which C.F. Codd, a mathematician and employee of IBM invented around 1970. This theory was the key to the development of the Relational Database model, from which the term stems, but also to the Distributed Database model over networked systems later, as then it became possible to view data structures in ways other than the original hierarchical, serial, \"flat-file\u201d oriented way \u2013 namely the network, semantic and relational views being the most common.<\/span><\/span><\/span><\/span><\/p>\n

A single centralised system database configuration was the only model in existence and being developed originally, until Codd\u2019s groundbreaking work, and still exists today as it still has certain advantages over the distributed database configuration.<\/span><\/span><\/span><\/span><\/p>\n

As this centralised\u00a0<\/span><\/span>configuration\u00a0<\/span><\/span>resides in only one physical location, on one machine, under the control of one Database Management System (DBMS), immediate concern in relation to data storage preservation is apparent because of the potential for loss of data that may occur through -local\u201d damage such as fire or flood, as there is no inherent means for total data duplication except locally. Any local back up procedure then relies completely on the removal and safekeeping of local copies to remote locations and keeping acceptable synchronisation timescales between latest versions and backup copies.<\/span><\/span><\/span><\/span><\/p>\n

However, there are certain situations where Database centralisation may be a requirement or a necessity - to prevent unauthorised access to sensitive information within a secure building environment for example - such as Military establishments or other, where the possibility of physical removal of information and data access locally can be closely controlled and monitored. External network access may even be prevented completely to reduce the potential for data interception on connections outside of the actual building complex.<\/span><\/span><\/span><\/span><\/p>\n

For any Database, there is an issue with time related data accuracy; essentially meaning -how up to date\u201d or -current\u201d is the most recent Database image in real time.<\/span><\/span><\/span><\/span><\/p>\n

Another advantage of the centralised model is that data accuracy is upheld in a relatively timely fashion compared to the Distributed, networked Database model \u2013 all things being equal - where multiple instances of the same Database may be running on different systems simultaneously - because -concurrent\u201d access is processed locally on one computer only running one instance of the Database, and each local -transaction\u201d (a completed user access that involves the changing of a data value in the database) is processed and completed\u00a0<\/span><\/span>before<\/i><\/span><\/span>\u00a0the next, concurrent user transaction can occur, so the database always reflects its most recent state of change between transactions to the user, hence a high degree of data accuracy is retained, provided no transaction errors have occurred, that would adversely affect the data -consistency\u201d. The more processing power available the better, naturally, so many centralised databases were originally run on Mainframe computers (and still are) to help speed up multi-user transaction times where the user was connected to the system by dumb terminals. Today, the user access to centralised and distributed database systems - controlled by local Database Management System software (DBMS) - is from networked computer clients running Client software in a Server\/Client configuration.<\/span><\/span><\/span><\/span><\/p>\n

There may be less data accuracy between user transactions with a distributed configuration, as there are multiple instances of the database running simultaneously on different systems that (may) each require -consolidation\u201d to a Master database instance at some point, which may not yet have occurred for a number of reasons such as systems inter-connection delay (latency), workload, or system processing time differences between multiple networked systems.<\/span><\/span><\/span><\/span><\/p>\n

The cost of data accuracy in the centralised system may come at the price of perceived user delay experience as there is only one instance of an available portion of data that can be accessed and changed at a time per user access. Not so with the distributed networked model - because multiple copies of the Database exist, but conversely, the database -image\u201d being accessed here may not be the most -up to date\u201d version that exists across those multiple instances.<\/span><\/span><\/span><\/span><\/p>\n

Factors such as network connection bandwidth, system processing power and user workload all affect both database models to a greater or lesser extent in general so have to be taken into consideration at the design stage and compromises made depending on the intended role of the Database. Methods have been invented and implemented to improve this simultaneous access\/concurrency time delay, whilst retaining acceptable levels of data accuracy.<\/span><\/span><\/span><\/span><\/p>\n

The Distributed Database model is inherently insecure from an unauthorised access standpoint compared to the Centralised model, mainly because the data is usually stored on multiple networks, so multiplying the potential for unauthorised access to multiple systems, but it has the major advantage of ensuring that complete data loss is minimal as the data can be copied from\/to, and stored on, multiple computers automatically that may be geographically dispersed worldwide, also it reduces user transaction delay as there are many instances of the same database serving many clients on multiple systems. Failover redundancy is inherent, as if one system fails another is available in its place.<\/span><\/span><\/span><\/span><\/p>\n

Note that in all the previous comparisons that these separate configurations can be combined, as here, in a study by\u00a0<\/span><\/span>Mehmet Savsar and Fawaz S. Al-Anzi,<\/span>\u00a0<\/b><\/span><\/span>evaluating the -<\/span><\/span>Reliability of Data Allocation on a Centralized Service Configuration with Distributed Servers\u201d<\/span><\/span>:<\/span><\/span><\/span><\/span><\/p>\n

-The centralized<\/i><\/span>\u00a0<\/i><\/span><\/sup>server monitors and controls the distributed\u00a0<\/i><\/span>database<\/i><\/span>\u00a0servers,<\/i><\/span>\u00a0<\/i><\/span><\/sup>which can be remotely separated from each other and from the<\/i><\/span>\u00a0<\/i><\/span><\/sup>center by almost any distance...\u201d<\/i><\/span><\/span><\/span><\/p>\n

https:\/\/comjnl.oxfordjournals.org\/cgi\/content\/abstract\/49\/3\/258?maxtoshow=&HITS=10&hits=10&RESULTFORMAT=1&andorexacttitle=and&andorexacttitleabs=and&fulltext=database&andorexactfulltext=and&searchid=1&FIRSTINDEX=10&sortspec=relevance&fdate=1\/1\/2004&resourcetype=HWCIT<\/i><\/span><\/span><\/span><\/a><\/span><\/span><\/p>\n

<\/h2>\n

The Client\/Server Configuration<\/h2>\n

This -data transfer\u201d model applies fundamentally to the Distributed Database configuration, as the term is a general networked computer data access process definition, rather than a Database model in itself. The model is indifferent to whether the client (user) is physically -local\u201d or -remote\u201d or what media (network cable, wireless etc.) is utilised to connect the client to the server. This model also applies to a relatively recent situation that has evolved, thanks to large amounts of memory, storage and processing power that are available for relatively very low cost in personal computers or laptops, where the database server software and the client software can reside on the same physical machine.<\/span><\/span><\/span><\/span><\/p>\n

Historically within the evolution of Database systems, this combination was not possible initially, as computer hardware was extremely expensive, of specific design, and the Operating System software for running both a server and client simultaneously on one computer did not exist. This data transfer model exists wherever there is data stored that requires accessing over a network by a user, whether a Database is involved or not.<\/span><\/span><\/span><\/span><\/p>\n

As there are many versions of both server Database Management Systems (DBMS) (-back-end\u201d) software, and client -query\u201d (-frontend\u201d) software available for free now, there are major benefits for the individual. A student for example, can take both roles of user and administrator for different -flavours\u201d (SQL, MySQL, MS Access etc.) of Database query and management tools, enabling the construction, administration and access to their own database relatively simple and cheap.<\/span><\/span><\/span><\/span><\/p>\n

2.\u00a0\u00a0\u00a0\u00a0 Describe how you would tackle the problem of ensuring data consistency and concurrency.<\/i><\/span><\/h1>\n

Data Concurrency<\/h2>\n

Concurrency was touched on earlier in respect to the access of data by users during simultaneous transactions, but not defined.<\/span><\/span><\/span><\/span><\/p>\n

Concurrency Definition\u00a0<\/span><\/b><\/i><\/span><\/span><\/span><\/i>-Concurrency is the ability of processes to interact with the data without encountering interference from concurrent processes. If the probability of interference is low, then the level of concurrency is said to be high. Increased concurrency translates into increased sharability and availability.\u201d<\/i><\/span><\/span><\/span><\/p>\n

Gordon C. Everest (1986)\u00a0<\/span><\/span>Database Management<\/i><\/span><\/span>, Singapore: McGraw-Hill Book Co.<\/span><\/span><\/span><\/span><\/p>\n

As stated earlier, user experience is directly affected by any transaction delay, yet ultimately, data accuracy is paramount, so the user experience will always be secondary to the integrity and accuracy of the data. Can any improvements be made in this respect, and if so how is it achieved?<\/span><\/span><\/span><\/span><\/p>\n

-Concurrent access control generally takes the form of<\/span><\/span><\/span><\/p>\n

data locking,\u00a0<\/span>i.e.,\u00a0<\/b><\/i><\/span>giving a user exclusive access to some<\/span><\/span><\/span><\/p>\n

part of the data base for as long as necessary to avoid interference.\u201d<\/span><\/span><\/span><\/p>\n

WC Mcgee (1981)\u00a0<\/span><\/span>Data Base Technology,<\/i><\/span><\/span>\u00a0PDF\u00a0<\/span><\/span>https:\/\/domino.watson.ibm.com\/tchjr\/journalindex.nsf\/c469af92ea9eceac85256bd50048567c\/18c2b2dadee8a44985256bfa0067f4d8?OpenDocument<\/span><\/span><\/span><\/a>\u00a0p1, p515\u00a0<\/span><\/span>IBM I.\u00a0<\/b><\/span><\/span>RES. DEVELOP. VOL.\u00a0<\/span><\/span>25 0\u00a0<\/b><\/i><\/span><\/span>NO.\u00a0<\/b><\/span><\/span><\/b><\/i>SEFTEMBER\u00a0<\/span><\/span>1981<\/b><\/span><\/span><\/span><\/span><\/p>\n

There are levels of -granularity\u201d that can be imposed during the access to data and to what degree a portion of data being accessed is -locked out\u201d to further transaction attempts while the current access is occurring, that effect the processing ability (overhead) of the system overall. For example, if the default level of lockout granularity is to lock the whole file (as in a network shared Word or PDF document), even if only a small section of it is being changed, then the granularity of locking is high, which translates to low system overhead, but it may be very inconvenient if another part of the file requires altering by someone else simultaneously. This analogous situation is unacceptable in a database environment as a database may contain thousands or even millions of related Tables and associated records, so the locking of a single related Table may cause an unacceptable level of transaction delay, as other related Tables may need to be locked until the transaction is complete. Obviously the degree of granularity for data-locking for databases needs to be much finer if required, at least to the individual record within a file level, or less, but the cost for this finer granularity is increased system overhead as the system has to identify smaller sets of data and hold each set in memory, and it may be that if multiple records within a file require locking that there would be less system resources used in just locking the whole file anyway.<\/span><\/span><\/span><\/span><\/p>\n

If a system has sufficient hardware resources available though, the finer the granularity of locking is desirable, as more of the whole database is available for further transactions to continue unhindered.<\/span><\/span><\/span><\/span><\/p>\n

There are two aspects to the locking process, which depend on whether a concurrent transaction request intends to write to the same data presently being accessed by the current transaction, or whether it intends to only read from it. This will decide whether or not and HOW the CURRENT transaction makes a request to the system to lock the data that it is currently accessing, to prevent potential interference from the CONCURRENT transaction. If it is a READ only request from the CONCURRENT transaction then the data is not locked from it, as only when data is potentially being altered by the CONCURRENT transaction is there a risk of data accuracy and integrity being threatened by CONCURRENT interference.<\/span><\/span><\/span><\/span><\/p>\n

Data Consistency<\/h2>\n

-IMS protects against data inconsistency...by recording all data base<\/span><\/span><\/span><\/p>\n

changes made by a program in a\u00a0<\/span>dynamic log.\u00a0<\/b><\/i><\/span>If the<\/span><\/span><\/span><\/p>\n

program reaches a synchpoint, its dynamic log entries are<\/span><\/span><\/span><\/p>\n

discarded, thereby committing its data changes. If the<\/span><\/span><\/span><\/p>\n

program ends abnormally...the system... uses the dynamic<\/span><\/span><\/span><\/p>\n

log to back out all data base changes...since its most recent synchpoint.<\/span><\/span><\/span><\/p>\n

WC Mcgee (1981)\u00a0<\/span><\/span>Data Base Technology,<\/i><\/span><\/span>\u00a0PDF\u00a0<\/span><\/span>https:\/\/domino.watson.ibm.com\/tchjr\/journalindex.nsf\/c469af92ea9eceac85256bd50048567c\/18c2b2dadee8a44985256bfa0067f4d8?OpenDocument<\/span><\/span><\/span><\/a>\u00a0p1,p505\u00a0<\/span><\/span>IBM I.\u00a0<\/b><\/span><\/span>RES. DEVELOP. VOL.\u00a0<\/span><\/span>25 0\u00a0<\/b><\/i><\/span><\/span>NO.\u00a0<\/b><\/span><\/span><\/b><\/i>SEFTEMBER\u00a0<\/span><\/span>1981<\/b><\/span><\/span><\/span><\/span><\/p>\n

Data consistency is achieved by the generation of -logs\u201d that keep a record of every transaction event that takes place, and the logs used in conjunction with periodic Full backups of the Database. In the event of Database failure or corruption, the Database can then be rebuilt up to the point of failure just from the last full back up, and then replaying the transaction logs since the last full backup, up to the point of failure. For possible drive failure\/corruption reasons, the log files should be stored on a different drive than the current Database instance itself, and preferably the log drives -mirrored\u201d so there is always an automatic duplicate created of the logs \u2013 as they are the only most recent versions in existence - for the same single drive failure reasons mentioned.<\/span><\/span><\/span><\/span><\/p>\n

3.\u00a0\u00a0\u00a0\u00a0 Explain what user grants and revokes are.<\/span><\/h1>\n

In Data Control Language (DCL), which is a subset of Server Query Language, a -Grant\u201d is the allowance privilege given to a user or a -role\u201d (e.g. an Administrator) for carrying out specific tasks, usually at a higher security level than their -normal\u201d user level would inherently allow, so the user\/role can make fundamental permanent changes to a Database.<\/span><\/span><\/span><\/span><\/p>\n

A -Revoke\u201d is the action of reversing previously allowed Grants by removing the permission of the user or role to perform that action any longer.<\/span><\/span><\/span><\/span><\/p>\n

In MySQL, these commands are usually of the form:<\/span><\/span><\/span><\/span><\/p>\n

-GRANT privileges ON db_name.* TO username IDENTIFIED BY \u00b4password\u00b4\u201d<\/i><\/span><\/span><\/span><\/span><\/p>\n

Larry Ullman (2003)\u00a0<\/span><\/span>MySQL,<\/i><\/span><\/span>\u00a0Berkley, CA: Peachpit Press<\/span><\/span><\/span><\/span><\/p>\n

It is important to note the use of the inclusion of the -<\/span><\/span>IDENTIFIED BY \u00b4password\u00b4\u201d\u00a0<\/i><\/span><\/span>addition to this command line for security reasons, and that the GRANT command also creates a new user account at the same time. If the GRANT is to be issued to a networked user then it is required to name the user and include the networked identity of the machine, usually by its IP address that takes the form -username@192.168.1.x\u201d. Some specific commands in MySQL that an Administrator can grant or revoke rights to a user for are:<\/span><\/span><\/span><\/span><\/p>\n

-SELECT \u2013 Read rows from tables<\/i><\/span><\/span><\/span><\/p>\n

INSERT \u2013 Add new rows of data to tables<\/i><\/span><\/span><\/span><\/p>\n

UPDATE \u2013 Alter existing data in tables<\/i><\/span><\/span><\/span><\/p>\n

DELETE \u2013 Remove existing data in tables<\/i><\/span><\/span><\/span><\/p>\n

CREATE \u2013 create new tables or databases<\/i><\/span><\/span><\/span><\/p>\n

DROP \u2013 Delete existing tables or databases\u201d<\/i><\/span><\/span><\/span><\/p>\n

Larry Ullman (2003)\u00a0<\/span><\/span>MySQL,<\/i><\/span><\/span>\u00a0Berkley, CA : Peachpit Press<\/span><\/span><\/span><\/span><\/p>\n

<\/h1>\n

4.\u00a0\u00a0\u00a0\u00a0 Define data integrity and explain how it may be implemented.<\/i><\/span><\/h1>\n

Data Integrity<\/h2>\n

-Incomplete and inaccurate data will destroy people\u00b4s confidence in the database....when a firm faces bankruptcy because destroyed data cannot be reconstructed, there is strong motivation to install integrity control mechanisms, both system control and administrative control\u201d<\/i><\/span><\/span><\/span><\/p>\n

Gordon C. Everest (1986)\u00a0<\/span><\/span>Database Management<\/i><\/span><\/span>, Singapore: McGraw-Hill Book Co.<\/span><\/span><\/span><\/span><\/p>\n

As a fundamental reason to create a Database is to -model\u201d a real world -entity\u201d whether it is an object, person or idea etc., by assigning one or more forms of descriptive identifiers to the object to be modelled \u2013 StudentName, StudentID and so on - these identifiers have to conform to the formats that are available for -containing\u201d the data within the limitations of the Database Management System software that translates to operate on top of the underlying computer system and are finite and of the forms that the computer can -understand\u201d \u2013 -Strings\u201d, integers, -characters\u201d etc. These are assigned in the Table design and creation phase, so that ONLY that particular legal form can apply to that specific datum that makes up part of the -description\u201d of the entity. For example, if an integer is assigned as the available form for the StudentID field, then ONLY an integer type value should exist in present and future occurrences of that particular column descriptor. If any other type can be entered in this field, either deliberately or erroneously, then the integrity of the data in the Table will be lost, and the original purpose of the Database to deliver what was intended, will be in question.<\/span><\/span><\/span><\/span><\/p>\n

Data Integrity Definition<\/span><\/b><\/i><\/span><\/span><\/span>\u00a0-<\/span><\/i><\/span><\/span><\/span>continued , ensured existence (of data) in the same format that was originally defined for it and no other\u201d.<\/i><\/span><\/span><\/span><\/p>\n

Integrity of data can be ensured by various types of cross-checking, and at the Table creation phase, as the datum type is defined at that point for each particular column. Checks can occur across other data fields in the same or related Tables where there is a relationship between them and so the expectation for a particular datum type in a particular field should correspond. These checks come in four categories: Domain (Column) integrity, Entity (Row) integrity, Referential (Table\/cell to Table\/cell) integrity and User-defined (Business Model) integrity.<\/span><\/span><\/span><\/span><\/p>\n

<\/a>Domain (Column) Integrity<\/span><\/b><\/span><\/span>\u00a0has been explained above generally, but a graphical representation may be helpful, as we can see that the Column descriptor can only be of the data type set for each column \u2013 integer, variable character - and also demonstrate how\u00a0<\/span><\/span>row and referential integrity\u00a0<\/b><\/span><\/span>between same and multiple Tables and cells can work. A relationship between the two tables \u2013 a Primary Key \u2013 that chooses a single commonality between them to enable a -uniqueness\u201d trait between them that can only exist for one individual student, Homer Simpson, in both tables which is his individual StudentID description<\/span><\/span><\/span><\/span><\/p>\n

Table1 Students<\/span><\/span><\/span><\/p>\n\n\n\n\n\n<\/colgroup>\n\n\n\n\n\n
\n

StudentID (int)<\/span><\/span><\/span><\/p>\n<\/td>\n

\n

StudentName(varchar < 60)<\/span><\/span><\/span><\/p>\n<\/td>\n

\n

CourseID(int)<\/span><\/span><\/span><\/p>\n<\/td>\n

\n

Campus (varchar < 30)<\/span><\/span><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

001<\/span><\/span><\/span><\/span><\/p>\n<\/td>\n

\n

Homer Simpson<\/span><\/span><\/span><\/p>\n<\/td>\n

<\/td>\n<\/td>\n<\/tr>\n
<\/td>\n<\/td>\n<\/td>\n<\/td>\n<\/tr>\n
<\/td>\n<\/td>\n<\/td>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Table2 Courses<\/span><\/span><\/span><\/p>\n\n\n\n\n\n<\/colgroup>\n\n\n\n\n\n
\n

CourseID (int)<\/span><\/span><\/span><\/p>\n<\/td>\n

\n

CourseName (varchar < 30)<\/span><\/span><\/span><\/p>\n<\/td>\n

\n

Course Start Date (char)<\/span><\/span><\/span><\/p>\n<\/td>\n

\n

StudentID (int)<\/span><\/span><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

54<\/span><\/span><\/span><\/p>\n<\/td>\n

\n

Nuclear Physics<\/span><\/span><\/span><\/p>\n<\/td>\n

<\/td>\n\n

001<\/span><\/span><\/span><\/span><\/p>\n<\/td>\n<\/tr>\n

<\/td>\n<\/td>\n<\/td>\n<\/td>\n<\/tr>\n
<\/td>\n<\/td>\n<\/td>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

Row Integrity<\/span><\/b><\/span><\/span>\u00a0is commonly checked using the above method of Primary Key or a Unique field that links two separate Tables, as this checks that the format of the table is correct horizontally, as these keys may be in different Columns in these two tables as in the above example. The two data fields are chosen that reflect the -uniqueness\u201d of the student \u2013 the StudentID number and StudentName combination would be ideal as the Primary Key, as even two students sharing the same name would have unique StudentID numbers so even when relating across tables an individual student can be uniquely identified.<\/span><\/span><\/span><\/span><\/p>\n

<\/a>Referential integrity<\/span><\/b><\/span><\/span>\u00a0checks can be done between data cells when they are linked in some way so have a common reference \u2013 maybe by a calculation on two fields and a resulting total, as in an Excel spreadsheet for example \u2013 where the computed value should only be within a certain limit, so that the row integrity checks for that result are within the designed acceptable range, or where the input to a data field can only be of one or two letters \u2013 M or F for a gender typing scenario \u2013 so any other character except those two are unacceptable and would indicate data error.<\/span><\/span><\/span><\/span><\/p>\n

<\/a>User-defined integrity (Business Model)<\/span><\/b><\/span><\/span>\u00a0<\/b><\/span><\/span>checks can be specified depending on the attribute required within a cell value and defined by the user, say an email address that must contain a -@\u201d character else report an error condition.<\/span><\/span><\/span><\/span><\/p>\n

From combinations of the four integrity check types possible within Tables, it can be seen that there are many possibilities for checking the overall integrity of data being entered into and stored correctly in Databases Tables.<\/span><\/span><\/span><\/span><\/p>\n

5.\u00a0\u00a0\u00a0\u00a0 Explain how applications can be developed using proprietary database tools.<\/i><\/span><\/i><\/h1>\n

Application Development<\/h2>\n

-Applications developers translate software requirements into concise and robust programming code. Most will specialise in a specific development environment, e.g. computer games or e-commerce, and will have in-depth knowledge of a few relevant computer languages. The role involves writing specifications and designing, building, testing, implementing and sometimes supporting applications using computer languages and development tools.<\/i><\/span><\/span>\u201d<\/i><\/span><\/span><\/span><\/p>\n

Gordon C. Everest (1986)\u00a0<\/span><\/span>Database Management<\/i><\/span><\/span>, Singapore: McGraw-Hill Book Co.<\/span><\/span><\/span><\/span><\/p>\n

The Database Management System provides the fundamental components with which to develop specific applications tailored to the user needs that suit their current Graphical User Interface and probable lack of programming language knowledge to enable the Application Developer to combine the required elements from low level and high level languages to create an appropriate application that fulfils their needs whilst shielding the user from the underlying complexity involved.<\/span><\/span><\/span><\/span><\/p>\n

This involves encapsulating what could be many cryptic lines of low level code or commands that are required to access and manipulate the database within something as easy as a mouse click on a menu or similar, in the users\u00b4 modern graphic environment. The main difference for the developer of this higher level application, is in the way the database as a complete structure in itself is viewed, so that complex data manipulation commands can be constructed from the lower level language syntax that is required for incorporation into the new application.<\/span><\/span><\/span><\/span><\/p>\n

Original proprietary database tools are combinations of programs and Data Manipulation language commands that were originally developed (usually) in house by large corporations like IBM who invested in, researched and developed Databases originally, so are their corporate property. This means that development of any applications by third parties, where access and usage rights to these tools and programs are required to develop other applications is usually done under some form of license agreement.<\/span><\/span><\/span><\/span><\/p>\n

It is possible to download a Database developer\u2019s kits from many DBMS system vendors that incorporate package deployment licensing.<\/span><\/span><\/span><\/span><\/p>\n

As with any program design, user input is required to establish what tasks the application would need to perform, and which may be desirable. Input from a Database Administrator would be invaluable in the applications development as he or she should be the one with the most knowledge of the systems base programs and tools and what various users would require in the application for querying the database.<\/span><\/span><\/span><\/span><\/p>\n

<\/h2>\n

<\/h2>\n

Bibliography<\/i><\/span><\/span><\/h2>\n