1. Towrite short notes on:a) DerivedattributeØ This is a function that maps from the entity set intoa domain, whereby its value can be derived from the valuesof other related attributes or entities.
For example the Lecturer entityset has an attribute age that indicates the Lecturer’s age. If the Lecturer entity set also has an attribute dateof birth, we can calculate age from date of birth and thecurrent date (Silberschatz Et Al. 2011).
b) DataFlow DiagramØ Data flow diagrams (DFDs) is the diagram which revealsthe relationships among and between the various components in a program orsystem. DFDs are an important technique for modeling a system’s high-level detailby showing how input data can be transformed to output results through asequence of functional transformations. DFDs consist of four major componentswhich are entities, processes, data stores, and data flows. (Donald 2017) Figure 1: data flow diagram; Retrieved from https://www.
visual-paradigm.com/tutorials/data-flow-diagram-example-supermarket-app.jspc) Database and DBMSØ The database is a collection ofdata, which stores the data required for a given task in an organised way,grants access to them, and at the same time safeguards the integrity of theunits, and protects them from any harm (Elmasri,Ramez.
2011).AndØ A database-managementsystem (DBMS) is acollection of interrelated data and a set of programs to access those data.d) ER ModelØ The ER model is the abbreviation of Entity RelationalModel. It defines the conceptual view of a database The ER model is veryuseful in mapping the meanings and interactions of real-world enterprises ontoa conceptual schema. Because of this usefulness, many database-design toolsdraw on concepts from the E-R model. The E-R data model uses the three basicconcepts which are entity sets, relationship sets, and attributes.e) SQL as a high level language used DBAccessØ The name SQL is presently expanded as StructuredQuery Language. It was originally called SEQUEL (Structured English QueryLanguage) and was designed and implemented at IBM Research as the interface foran experimental relational database system called SYSTEM R.
An SQL language isnow the standard language for commercial relational DBMS’s.2. To define Relational Database and distinguishit from a Table. Ø Relational Database isa database which is based on therelational model that means it presents information intables with rows and columns. It usesa collection of tables to represent both data and the relationships among thosedata. It also includes a DML and DDLØ A table is referred to as arelation in the sense that it is a collection of objects of the same type(rows). Thus the major difference between therelational database and table is:Ø Data in a table can be related accordingto common keys or concepts, and the ability to retrieve related data from atable is the basis for the term relational database.3.
.A 3-Tier Architecture separates its Tiers from each other based on thecomplexity of the users and how they use the data present in the DB, hence themost widely used Architecture to design a DBMSBy use of the 3-TierArchitecture, illustrate how the functional process Logic, Data Access,Computer Data Storage and User Interface are developed and maintained asIndependent modules on separate platforms. Solution:The 3-Tier Architecturefacilitates what is called data independence. Data independence can be defined as the capacity to change the schema atone level of a database system without having to change the schema at the nexthigher level. We can define two types of data independence as Logical dataindependence and physical data independence.By using3-tier architecture the functional process logic must be separated fromData Access, Computer Data Storage and User Interface as follows using the twotypes of independencies; Ø Logical data independence;This is thecapacity to change the conceptual schema without having to change externalschemas or application programs. We may change the conceptual schema to expandthe database (by adding a record type or data item), to change constraints, orto reduce the database (by removing a record type or data item) in a way thatexternal schemas that refer only to the remaining data should not be affected.
Here the logical data is separated from physical data (that is data access,user interface and computer Data storage is separated) (Shweta, 2009). The following isthe diagram showing the independencies; Figure 2: logical data independence; retrieved from http://player.slideplayer.com/16/5212349/#Ø Physical data independence:This is thecapacity to change the internal schema without having to change the conceptualschema. Hence, the external schemas should not be changed as well.
Changes tothe internal schema is needed because some physical files were reorganized, forexample, by creating additional access structures to improve the performance ofretrieval or update. If the same data as before remains in the database, weshould not have to change the conceptual schema. This means that physical data(data access, user interface and computer Data storage) is separated from the logicaldata (conceptual schema) (Shweta, 2009). Figure3 : Physical data independence; retrieved from https://www.tutorialcup.
com/dbms/data-independence.htm4. a)To mention and explain at least four Database utilities.
i. Loading utilities: A loading utilityis used to load existing data files-such as text files or sequential files-intothe database. Usually, the current (source) format of the data file and thedesired (target) database file structure are specified to the utility, whichthen automatically reformats the data and stores it in the database. With theproliferation of DBMSs, transferring data from one DBMS to another is becomingcommon in many organizations. Some vendors are offering products that generatethe appropriate loading programs, given the existing source and target databasestorage descriptions (internal schemas).
Such tools are also called conversiontools (Shweta, 2009). ii. Backup utilities: Abackup utility creates a backup copy of the database, usually by dumping theentire database onto tape. The backup copy can be used to restore the database incase of catastrophic failure. Incremental backups are also often used, where onlychanges since the previous backup are recorded. Incremental backup is morecomplex but saves space (Shweta, 2009). iii.
File Reorganization utilities: This utility canbe used to reorganize a database file into a different file organization toimprove performance. iv. Performance Monitoring utilities: Such a utilitymonitors database usage and provides statistics to the DBA. The DBA uses thestatistics in making decisions such as whether or not to reorganize files toimprove performance (Shweta, 2009). b) Define andwrite short notes on: i. DDLData-definitionlanguage (DDL). TheDDL provides commands for defining relation schemas, deleting relations, andmodifying relation schemas used by the DBA and by database designers to defineboth schemas.
The DBMS will have a DDL compiler whose function is to processDDL statements in order to identify descriptions of the schema constructs andto store the schema description in the catalog. Examples of commands statementsfor DDL are CREATE, ALTER, DROP, TRUNCATE, COMMENT, and RENAME (Tibor, 2011). ii. DMLOnce the databaseschemas are compiled and the database is populated with data, users must havesome means to manipulate the database. Typical manipulations include retrieval,insertion, deletion, and modification of the data. The DBMS provides a set ofoperations or a language called the data manipulation language (DML)for these purposes. The examples of DML statements are SELECT, INSERT, UPDATE,DELETE, MERGE, CALL, EXPLAIN PLAN and LOCK TABLE.
iii. Attribute:An attribute of anentity set is a function that maps from the entity set into a domain. Since anentity set may have several attributes, each entity can be described by a setof (attribute, data value) pairs, one pair for each attribute of the entity set.They are descriptive properties possessed by each member of an entity set. It expressesthat the database stores similar information concerning each entity in theentity set; however, each entity may have its own value for each attribute. iv.
Relationshipin DBMS A relationship is an association among several entities. Relationshipsare mapped with entities in various ways. Mapping cardinalities define thenumber of association between two entities.
Mapping cardinalities: one to one,one to many, many to one, many to many5. a) To explain the advantages of DBMS i. Data independence and efficient access. Since database application programs are independent of the details ofdata representation and storage, this means that the conceptual and externalschemas provide independence from physical storage decisions and logical designdecisions respectively. Also a DBMS provides efficient storage and retrievalmechanisms, including support for very large files, index structures and query optimization. ii. Reduced application development time. Since the DBMS provides several important functionsrequired by applications, such as concurrency control and crash recovery, highlevel query facilities, etc.
, only application-specific code needs to bewritten. Even this is facilitated by suites of application development tools availablefrom vendors for many database management systems. iii. Data integrity and security.The view mechanism and the authorization facilities of a DBMS provide apowerful access control mechanism. Further, updates to the data that violatethe semantics of the data can be detected and rejected by the DBMS if usersspecify the appropriate integrityconstraints. iv. Data administration.
By providing a common umbrella for a large collection of data that isshared by several users, a DBMS facilitates maintenance and data administrationtasks. A good DBA can effectively shield end-users from the chores offine-tuning the data representation and periodic back-ups. v.
Concurrent access A DBMS supports the notion of a transaction, which is conceptually a single user’ssequential program. Users can write transactions as if their programs wererunning in isolation against the database. The DBMS executes the actions oftransactions in an interleaved fashion to obtain good performance, butschedules them in such a way as to ensure that conflicting operations are notpermitted to proceed concurrently. vi. Crash recovery.The DBMS maintains a continuous log of the changes to the data, and ifthere is a system crash, it can restore the database to a transaction-consistent state. That is, theactions of incomplete transactions are undone, so that the database statereflects only the actions of completed transactions. Thus, if each completetransaction, executing alone, maintains the consistency criteria, then thedatabase state after recovery from a crash is consistent.
b) ToCompare and contrast the Traditional System and the DBMS Ø Difference betweenFile processing system and DBMS: i. A database management system (DBMS)coordinates both the physical and the logical access to the data,whereas a file-processing system coordinates only the physical access. ii. A database management system isdesigned to allow flexible access to data (i.e. queries), whereas afile-processing system is designed to allow predetermined access to data (i.e.compiled programs).
iii. A database management system isdesigned to coordinate multiple users accessing the same data at the same time whilea file-processing system is usually designed to allow one or more programs toaccess different data files at the same time. In a file-processing system, afile can be accessed by two programs concurrently only if both programs haveread-only access to the file. iv. Redundancy is control in DBMS, butnot in file system. v. Unauthorized access is restrictedin DBMS but not in the file system.
vi. DBMS provide backup and recoverywhereas data lost in file system can’t be recovered. vii.
DBMS provide multiple userinterfaces while Data is isolated in file system. Ø Similarities between DBMSand file-processing system: i. Both DBMSand file-processing system are storing data on apermanent storage media. For example, both have the concept of block levelstorage that is database blocks and filesystem blocks – which is the smallestcontiguous number of bytes for physically storing the information.
ii. Both database and file-processingsystem have “indexes” which speed upaccess to the data. There is also aconsideration of migration where by both database and filesystem migrationentailed one key requirements: whenever data is being move, readingand writing to the data must be halted/disabled. The migration can be onlinemigration or offline migration (Elmasri, Ramez.
2011). iii. Both filesystem and database concept havethe four key element properties of transactions that is ACID.
ACID stands for Atomicity (Atomicity represents the fact that a transaction is anindivisible unit of execution),Consistency (Consistencydemands that the carrying out of the transaction does not violate any of theintegrity constraints defined on the database),Isolation (Isolation demandsthat the execution of a transaction is independent of the simultaneousexecution of other transactions) and Durability (demands thatthe effect of a transaction that has correctly executed a commit is not lost.In practice, a database must guarantee that no piece of data is lost for any reason(Elmasri, Ramez, 2011). REFERENCESDonald S (2017). Understanding Data Flow DiagramsRetrieved from https://ratandon.mysite.syr.edu/cis453/notes/DFD_over_Flowcharts.pdfon 12 December 2017 at 15:16 PM.
Elmasri, Ramez. (2011).Fundamentals of databasesystems.6th edition.
New York. PearsonEducation Shweta (2009). Database Management System (DBMS).retrievedfrom http://database-management-systems.blogspot.
com/2009/09/database-system-utilities.html Silberschatz EtAl. (2011). Database system concepts, sixth edition. New York, NY 10020.
The McGraw-Hill Companies Tibor(2011). Advanced DBMS retrieved from aries.ektf.hu/~hz/pdf-tamop/pdf-xx/Radvanyi-hdbms-eng2.pdfon December 13, 2017 at 15:06 PM