Oracle Database Management System and the Object Oriented Data Model Overview - Coursework Example

Name Tutor Course Date Stage I Horizon Tech University established with the vision of building technical capacity has the sole objective of offering quality technical education and opportunity for further intellectual development consisting of undergraduate and post graduate courses. The institution’s director has approached Pneuma System Solutions to initiate a feasibility study with an aim of developing and implementing an Oracle database at the university’s main campus. The institution is currently departmentalized into the major department Academics, Administration, Admission and Finance. This is supposed to be a pilot project that will focus on the Academics department (Academics programmes) later enjoining the other departments. The director’s main requirement is that the current data capturing and processing can be streamlined and that these data can be queried to produce on line and real time reports concerning various departmental processes and that it can be distributed on the university’s network later on and be able to be accessed from the fourteen constituent campuses of the university. The university is the sole sponsor of the project and they want the database to be up and running within six months. The current database is running on standalone machines running the Microsoft Office applications especially so Access 2003 to store the university data. They employ query and report generation tools within Access to retrieve data and generate a number of reports based on parameters a user submits to the system. They have recently incorporated the crystal report 10 report generation tools which however have not produced the desired results. The system has continued to be greatly limited and prone to crashes because of the increasing demand for true relational database management system capabilities such as those to be provided by the Oracle database management system. Based on the fact that there already exists a file based management system running on Microsoft Office applications a migration to Oracle will be effected after the hardware platform necessary has been implemented. However for the purpose of gathering the relevant facts about the current system the document inspection process will be used mainly where an examination of the current system reports and outputs will be drawn to accurately derive the user requirements. Interviews will also be conducted involving the staff concerned in the academics and administration departments to get the clear picture. Pneuma System Solutions will also use observation as a technique to gather the necessary facts to proceed with this project. The Oracle RDBMS Overview and Suitability to this project Oracle have to their credit the first commercial version of relational technology released in 1977- 84 which was dubbed the pioneer phase. The management information (MIS) phase followed from 1984 – 88. The final major phase of development for Oracle was from 1988 – 92 the Online Transaction Processing (OLTP) phase. During the 1980s concern was raised due to the large number of database management systems (DBMS). This led to the introduction of Codd’s rules on relational DBMS. Oracle fulfills these rules making use of tables as its primary data structures , utilizing primary and foreign keys and null characters to represent incomplete or missing data. Oracle also offers an online catalog , uses SQL as its defined interface and permits the storage of integrity constraints in the system’s catalog. Oracle also includes object based features within the relational database system (Burleson Consulting). Modern day DBMS like Oracle comes in three main parts namely kernel, interface and toolkit. The kernel of the Oracle DBMS comprises a relational database engine with standard features such as concurrency control, transaction management, dictionary and query management. The kernel is accessed via SQL. The toolkit ha the following functions: Database administration where the Oracle Database Administrator (DBA) enables creation, maintenance, start, stop and recover a database. Oracle provides the means of integrating SQL with standard programming languages and tools for construction of data entry forms, reports and graphs under application development. Oracle also has facilities for writing stored procedures and triggers. Database Development Methodology. The steps in a database development approach include requirement elicitation, logical, conceptual and physical modelling. (See the diagrams in the appendix.) The requirement elicitation step will involve identifying key technical requirements. This is usually by formal and informal interaction between the developer and users. One key aspect of this step is the determination of the scope of the universe of discourse (UOD) to be covered by the proposed database system (see figure1 at the appendix) Conceptual modelling consists of building models of the real world in terms of data requirements (Nijssen and Haplin 120). In view modelling we develop an application data model relating to a business area. In view integration we take several of these distinct views in order to produce an integrated data model. (See figure 2 in the appendix) A Logical model represents the real world in terms of the data model principles. Physical modelling will involve the construction of a model of the real world in terms of data structures and access mechanisms mapped on a chosen DBMS. Physical modelling involves two distinct sub processes. These processes are physical database design and database implementation (Davies 196). Database implementation process will involve acquiring output from the physical design and implementing it as in the plan of a chosen DBMS (see figure 3 in the appendix) A summary of the steps is tabulated also (see table 1 in the appendix) The University’s Relational Data Model Relational data models support the relation or table as the only data structure. A relation will obey a certain set of rules. Every relation or table in the database must have a distinct name. Every column in the relation must have a distinct name (Davies 95). Every column in a relation has a distinct name within the relation. Every entry in a column should be the same kind. Ordering of rows and columns is insignificant. Each row must be distinct. Each cell or column / row intersection in a relation should contain only an atomic value. Typical to this project are the following relations below illustrating this fact Modules moduleName level courseCode staffNo Relational Database Systems 1 CSD 244 Relational Database Design 1 CSD 244 Deductive Databases 4 CSD 445 Object oriented Databases 4 CSD 445 Distributed Databases 2 CSD 247 Lecturers staffNo staffName status 244 Davies T L 247 Patel P SL 445 Evans R PL Courses courseCode moduleName CSD Relational Database Systems Relational Database Design Deductive Databases Object Oriented Databases Distributed Database Systems BSD Introduction to Business Basic Accounting Each relation must have a primary key to enforce the property that duplicating rows are not allowed. The primary key must be uniquely identifying each row. In a relation there may be a combination of columns from among which the primary key is chosen. These are called candidate keys. Considering our University database example we can see that the Lecturer relation has the attributes staffNo, staffName and status as the candidate keys. We know in practice that as the size of the lecturer’s table or relation grows we may store information about more than one lecturer and this may likely be a senior lecturer SL. THE ENTITY RELATIONSHIP DIAGRAM REPRESENTING THE ACADEMIC DATA MODEL Implementing the University Academic Database in Oracle The physical design will yield volumes, integrity, usage and transaction, distribution and control and security analyses. The main outputs from the database implementation will be data structures declared in suitable data definition language (DDL), a plan for securing the data, a set of defined users, the distribution strategy, a set of inherent constraints and indexes declared on the data structures. The major elements of this database schema will be created using the CREATE TABLE command of SQL a typical Data Manipulation Language in the Oracle RDBMS. The following are the tables as implemented in Oracle using SQL. CREATE TABLE Courses ( courseCode INTEGER(3), moduleName CHARACTER(30), Constraint c_pk PRIMARY KEY (courseCode) NOT NULL Constraint c_fk FOREIGN KEY (moduleName) DELETED RESTRICTED UPDATE CASCADES) The statements above will implement the Courses table including all the referential integrity constraints CREATE TABLE Lecturers( staffNo INTEGER(3), staffName CHARACTER(30), sHomeAddress CHARACTER (30), sHomeTelephone CHARACTER(17), Constraint l_pk PRIMARY KEY (staffNo) NOT NULL DELETED RESTRICTED UPDATE CASCADES) The statements above will implement the Lecturers table including all the referential integrity constraints CREATE TABLE Student( studentNo INTEGER (6), studentName CHARACTER (30), studentAddress CHARACTER (50)’ studentTelNo CHARACTER (15)’ Constraint s_pk PRIMARY KEY (studentNo) NOT NULL Constraint s_fk FOREIGN KEY (moduleName) DELETED RESTRICTED UPDATE CASCADES) The statements above will implement the Students table including all the referential integrity constraints CREATE TABLE Assessment ( moduleName CHARACTER (30), studentNo INTEGER(6), assType CHARACTER (5), aDate DATE, assGrade CHARACTER (2) studentName CHARACTER (30), staffNo INTEGER(3), Constraint a_pk PRIMARY KEY (moduleName) NOT NULL Constraint a_fk FOREIGN KEY(studentNo,staffNo) DELETE RESTRICTED UPDATE CASCADES) The statements above will implement the Assessment table including all the referential integrity constraints CREATE TABLE Modules( moduleName CHARACTER (15), level NUMBER (1)CONSTRAINT m_level DEFAULT(1)NOT NULL courseCode CHARACTER (3) staffNo INTEGER (3) Constraint m_pk PRIMARY KEY (moduleName) Constraint m_fk FOREIGN KEY (staffNo REFERENCES Lecturers) DELETE RESTRICTED UPDATE CASCADES Constraint m_fk FOREIGN KEY (courseCode REFERENCES Courses) DELETE RESTRICTED UPDATE CASCADES) The statements above will implement the Modules table including all the referential integrity constraints Normalizing the database A sample dataset shown here below (unnormalized data set) has been given by the administrative staff during fact finding. A quick analysis of the sample indicates a situation likely to promote file maintenance anomalies (Connolly and Beggs 388). These anomalies are likely to affect the deletion, insertion and update processes hence the need to normalize the table. Modules moduleName staffNo staffName studentNo Student assGrade assType Relational Database Systems 234 Davies T 34698 Smith S B3 Cwk1 B1 Cwk2 37798 Jones S B2 Cwk1 34888 Patel P B1 Cwk1 B3 Cwk2 Relational Database Design 234 Davies T 34698 Smith S B2 Cwk1 Deductive Databases 345 Evans R 34668 Smith J A1 exam The attributes studentNo, studentName, assGrade and assType are not functionally dependant on moduleName which is the primary key. While the attributes staffNo and staffName are hence we have two tables in 1st normal form as shown below. Modules moduleName staffNo staffName Relational Database Systems 234 Davies T Relational Database Design 234 Davies T Relational Database Design 234 Davies T Deductive Databases 345 Evans R Assessments moduleName studentNo assType studentName assGrade Relational Database Systems 34698 Cwk1 Smith S B3 Relational Database system 34698 Cwk2 Smith S B1 Relational Database system 37798 Cwk1 Jones S B2 Relational Database system 34888 Cwk1 Patel P B1 Relational Database system 34888 Cwk2 Patel P B3 Relational Database Design 34698 Cwk1 Smith S B2 Relational Database Design 34698 Cwk2 Smith S B3 Deductive Databases 34668 exam Smith J A1 “A relation is said to be in 2nd normal form if and only if it is in 1st normal form and every non- key attribute is fully functionally dependant on the primary key”(Davies 217). Thus to move from 1st normal form to 2nd normal form we remove part key dependencies as shown below Modules moduleName staffNo staffName Relational Database Systems 234 Davies T Relational Database Design 234 Davies T Relational Database Design 234 Davies T Deductive Databases 345 Evans R Assessments moduleName studentNo assType assGrade Relational Database Systems 34698 Cwk1 B3 Relational Database Systems 34698 Cwk2 B1 Relational Database Systems 37798 Cwk1 B2 Relational Database Systems 34888 Cwk1 B1 Relational Database Systems 34888 Cwk2 B3 Relational Database Design 34698 Cwk1 B2 Relational Database Design 34698 Cwk2 B3 Deductive Databases 34668 exam A1 Students studentNo studentName 34698 Smith S 37798 Jones S 34888 Patel P 34668 Smith J “A relation is in 3rd normal form if and only if it is in 2nd normal form and every non – key attribute is non transitively dependent on the primary key” (Davies 218). Therefore to move to 3rd normal from 2nd normal form we remove interdependencies. We must examine and compare the fields in the tables above separating the dependant non key data items as shown in the tables below. Our table / relation of interest is the modules Modules moduleName staffNo Relational Database Systems 234 Relational Database Design 234 Relational Database Design 234 Deductive Databases 345 Lecturers staffNo staffName 234 Davies T 345 Evans R Assessments moduleName studentNo assType assGrade Relational Database Systems 34698 Cwk1 B3 Relational Database Systems 34698 Cwk2 B1 Relational Database Systems 37798 Cwk1 B2 Relational Database Systems 34888 Cwk1 B1 Relational Database Systems 34888 Cwk2 B3 Relational Database Design 34698 Cwk1 B2 Relational Database Design 34698 Cwk2 B3 Deductive Databases 34668 exam A1 Students studentNo studentName 34698 Smith S 37798 Jones S 34888 Patel P 34668 Smith J The Object Oriented Data Model Overview Object oriented database systems will require existence of persistent objects. In object oriented programming languages objects exist only for the span of program execution. In object oriented database systems objects have a life in secondary storage over and above the execution of programs. An OO database is made up of objects and object classes linked through a number of abstraction mechanisms. An object is a package of data and procedure. Data are contained in attributes of an object. Procedures are defined by an object’s methods. Methods are activated by messages passed between objects (Davies 73). An OO data model must provide support for object identity which is the ability to distinguish between two objects with the same characteristics. All objects must demonstrate encapsulation which is the process of packaging together of data and process within a defined interface and controlled access across that interface.An object class defines a group of similar objects. It is used to define attributes, methods and relationships common to a group of objects. Object is therefore instances of some class. Object classes define the intension of an OO database the central topic of database design (Davies 74). Objects define the extension of an OO database which is core to database implementation. Abstraction mechanism An object data model supports two mechanisms allowing the database builder to construct hierarchies of lattices of object classes. Two such abstraction are generalization and aggregation. Implicit in the construction of object classes is the support for abstraction mechanism of generalization allowing for the declaration of certain objects classes as sub classes of other (Davies 74). Aggregation is the process in which a higher level of object is used to group together a number of lower level objects. Inheritance is either structural or behavioral. In structural inheritance a sub class inherits the attributes of the super class. In behavioral inheritance a sub class inherits the methods of the super class. Defining a schema using the OO approach For an OO database defining the schema means defining object classes. For instance we can define three simple object classes (Student, Postgraduate and Assessment) for the University database using the following statements CLASS Student SUPERCLASS: Object ATTRIBUTE studentName :CHARACTER ATTRIBUTE termAddress : CHARACATER ATTRIBUTE dateOfBirth : DATE ATTRIBUTE dateOfEnrolment : DATE CLASS Undergraduate SUPERCLASS: student RELATIONSHIP SET (Course) membersOf INVERSE enrolls CLASS Postgraduate SUPERCLASS : student RELATIONSHIP departmentAssignedTo INVERSE registers CLASS Assessment ATTRIBUTE studentDetails: student ATTRIBUTE moduleDetails: module Stage II Data warehouse and OLAP implementation A data warehouse defines a contemporary database system which has been designed to fulfill decision support needs of an organization such as the university (Davies 540). It is a subject oriented, integrated, time variant and non volatile. Implementing a data warehouse for the university will expressly benefit the institution in that it will enable a number of business intelligence applications such as online analytical processing (OLAP) to be employed (Kroenke 250). It will enable the users to run complex queries on data traversing different areas. A data warehouse provides a single manageable structure for effective decision support. Converting the data model to proposed data warehouse The steps followed in implementing this university data warehouse will include the following: 1) Specifying user information needs for instance the various lecturers at the different campuses may want adhoc reports on the assessment progress of the various students they are handling. This is an analytical process that can be implemented by making a facts table to include the assessment details for the various students (see diagram below). 2) Creating the logical and physical designs is a paramount step in database design due to the fact that the two views the logical and physical will produce a more thorough picture eliminating any ambiguity even in the eventual design and implementation. The database schema implemented earlier including the tables Modules, Assessment, Students, Lecturers and Courses will now be considered during this step. These table structures may need to be regrouped for example the Assessment table will have dateSet, dateReturned, level and moduleNo introduced. This restructuring may introduce some redundancy. This however is necessary in order to ensure efficient OLAP implementation. 3) Identifying sources of the data in operational systems in this case the units within the Academics department. Since the user requirement relates to data about the students and their assessment, the module, lecturers, student and assessment tables will be considered during this process of identifying the source data. The facts table which will be the StudentAssessment table will be linked to the student, lecturer and Assessment as dimensions. For OLAP implementation their may be need to de-normalize some of these relations to improve efficiency in the eventual data warehouse 4) Extracting and transforming these data which is where based on the star schema adopted the data is re-arranged into the facts table and associate dimensions. 5) Loading the warehouse periodically with updated data so that the resulting analytical reports reflect the accurate position. 6) Allowing user access to the warehouse data by assigning of privileges and rights which can be initiated using the administrative tools within Oracle. Certain groups of people will have different privileges even concerning the data in the warehouse. Various security levels can be established and administered by the database administrator. 7) Maintenance of hardware and the data warehouse Implement the data warehouse Designing a schema for a data warehouse does not greatly vary from that of the conventional database. However with the warehouse two issues assume prominence the large volume of data and initiating and achieving satisfactory levels of retrieval performance. Data warehouse schema commonly applicable are the star schema where factual data is stored in a central table surrounded by reference tables storing data relevant to particular dimensions needed for decision support. Each reference table may be a de-normalized version of a number of other tables in order to improve retrieval performance. During this project we use the star schema approach to implement the university data warehouse. Data can then be aggregated for example modules data can be aggregated into courses data and courses data into campuses, schools or departments’ data. Similarly data may be broken down from campus or school based into courses or modules in a process called drilling down. Pivoting can also be applied here where same data is analyzed from different view points to yield different perceptions for effective decision making. For example using the university domain one slice may be the average degree grade per course within a school or campus. Another slice would be the average degree grade per age group within a school or campus. A typical star schema for the university is illustrated in the diagram below The snowflakes schema is a variation of the star schema. Each dimension has a number of its own dimensions implying that the tables are not de-normalized (Kroenke 256). The star flakes schema occupies a middle ground between star and snowflakes schemas with some of the tables de-normalized while others remain normalized. Some of the likely challenges during the implementation of this data warehouse will include the following: 1) Identifying in advance the data users’ requirements and determining ownership and responsibility in terms of the data sources and security 2) Identifying, reconciling and cleaning existing data and loading it into the warehouse may be problematic 3) Selection, installation and integration of different hardware and software posses a challenge to harmonized processing. 4) It will be difficult to modify the data warehouse structure if the university changes their fundamental processes. 5) A lot of work during data warehouse implementation involves making similar meaning data consistent. However the master data management tool can be employed to extract, transform and load the data in the data warehouse. Self reflection and evaluation The continued exposure to database design and implementation has given me an opportunity to put to use some of the very useful tools packaged together with a commercial database management system such as Oracle. While reflecting on my experience with Oracle 9i, I have build on this confidence to help me study release 10 and 11 of Oracle which offer much more in terms of tools and hardware usage and optimization resulting in more efficient database systems. Erwin a tool within Oracle has proved to be helpful enabling the creation of databases from data models. While studying this tool I learnt that it is vital to this project due to the fact that you can use it to create dimensional data models which are the requirements for data warehouse implementation. The more one uses the tool kit within Oracle the more they are able to realize the enormous potential inherent within Oracle as a commercial DBMS. As such the implementation of this project in Oracle 10 or 11 or a later version is going to put the powerful OLAP tools at the user’s fingertips and it is envisaged that with a proper user training session conducted the university is going to reap maximum benefit from this implementation hence satisfying their requirements. Time was a limitation even in the pursuit of skills and experience. A fully functional Oracle platform is only available on an enterprise platform and the associated costs of installation and use are prohibitive. Therefore continued exposure that should build the experience and skill will be hindered by this fact. This study also reveals another dimension of database design based on the dimensional data model from which the data warehouse can be derived. While relational data modelling is used in on line transaction processing (OLTP) and is specifically transaction oriented, dimensional modelling is suitable for online analytical processing (OLAP). This makes the implementation of OLAP a little more varied than OLTP databases. Works cited Burleson Consulting. Oracle Concepts – RDBMS Architecture. 2010. 15th June 2010 Connolly Thomas and Begg Carolyn. Database Systems: A practical approach to design, implementation and management 3rd Ed. Essex : Addison Wesley, 2001. Davis Benyon Paul. Database Systems .3rd Ed.Hampshire : Palgrave Macmillan,2003. Kroenke David. Database Processing : Fundamentals Design & Implementation. Upper Saddle River: Prentice Hill, 2002. Nijssen G.M and Haplin A.T ed.Conceptual Schema and Relational Database Design : A fact oriented approach. Upper Saddle River : Prentice Hill, 1989. APPENDIX TABLE 1. The table below summarizes the database development process. Requirements Analysis Conceptual modelling : View modelling Defining entities / objects Defining relationships and constraints on relationships Defining attributes Defining abstraction mechanisms Defining behaviour View integration Identifying communalities among views Producing a global conceptual model Accommodating the conceptual model to a relational schema Logical modelling : Normalization Producing a 3NF schema through non –loss decomposition Producing a 3NF schema through a dependency analysis Reconciling the normalized schema with the schema produced from the conceptual modelling Physical modelling : Physical database design Volume analysis Usage / transaction analysis Integrity analysis Control / security analysis Distribution analysis Database implementation Selecting the DBMS Creating the physical schema Establish the storage structures with associated mechanisms of access Adding indexes De- normalization Defining users and privileges Tuning the chosen DBMS Building integrity constraints The diagram illustrates the database development methodology CONCEPTUAL MODELLING The diagram illustrating the conceptual Modelling process. FIGURE 3: PHYSICAL MODELLING PROCESS PHYSICAL MODELLING Read More