Database Design and Management PDF

Topic 1: GENERAL OVERVIEW OF THE COURSE – concepts and definitions Learning outcomes By the end of this topic, you should be able to: 1. gain a broad appreciation of scope of the course 2. define what is meant by database 3. define what is meant by dat...

Topic 1: GENERAL OVERVIEW OF THE COURSE – concepts and definitions Learning outcomes By the end of this topic, you should be able to: 1. gain a broad appreciation of scope of the course 2. define what is meant by database 3. define what is meant by database design LEARNING OUTCOMES 4. define what is meant by database management 5. identify the various categories of database 6. identify database application areas (case areas or where applicable) 7. strength and weakness of database design and management 8. preview of other topics 9. understand the relationship between internet, extranet, and intranet 10. understand e-commerce role against the traditional approach 11. understand some e-commerce terms 1. DATABASE DESIGN AND MANAGEMENT (DATABASE) 1.1 gain a broad appreciation of scope of the course Datum, data, database management systems, database, and database systems are the procedures or arrangements for handling and managing corporate records or information (see the next section). A database is a group of interrelated files. It is an organised collection of data associated with a particular subject. It may be created and manipulated manually or digitally (computerised). Examples are student database and customer database. Student database may include registration file (table), course file, and payment file. Customer database can also include registration file, order file, and payment file. In the context of database design and management there is a need to understand these three terms or words. Database is a software application designed to store large volume of related files. Design in this context is an approach or a process used to develop the database systems or application. For instance, how a student or developer can describe a particular organisation with the associated data and datatypes when developing a database. Also, what approaches or factors are required when creating or developing a database. Management also means process puts in place to handle or consolidate captured data and information in organisation’s database that can be integrated, shared, and accessed by other associated or compatible applications. Thus, what measures are required by experts to manage, analyse, use, and safeguard the content of organisational database. Database Design, therefore, means factors put in place to develop a real-world enterprise for handling large volume of data or integrated data. It is an approach for organising computerised systems for handling corporate data. The aspect of database management systems (DBMS) in computing in general is broad such as object- oriented programming, concurrent programming, data structures, theory, structured query language (SQL), algorithms, artificial intelligence, user interfaces, and parallel and distributed systems. But this Database design and mgt. Topic 1 by Dr. Ohene-Bonsu Simmons, Ph.D. Page 1 course will cover some selected themes or areas. More specifically, this course will emphasise some aspect for designing and managing a database with computerised systems, using MS Access. In the real-world, the idea of database in computing involves capturing, storing, processing, and disseminating data and information. These are the major application areas of database. Examples of database applications (DBMS) for a commercial business or managing corporate data are GCTU student results systems for publishing outcomes of examinations, ECG systems for making online bill payment, and SABRE systems for making online airline reservations. In this contemporary world, all the databases are Internet driven where users capture and access their data or profiles in real-time irrespective of the location. A database has no storage limit of data and information, it can be of any storage size according to the vendor’s design or standard. Some vendors in the database systems who developed the applications include Microsoft, Oracle, PeopleSoft, SAP, and IBM. These vendors also have advanced with other technology called data warehouse. This technology enables experts to consolidate data from different databases across the globe for analysis and decision making. 1.2 define what is meant by database and the rest Datum is a singular part of data. Data is a raw fact that can be captured or recorded without a meaning. It is a valuable asset or resource in digital economy and requires effective management processes such as capturing, storing, cleaning, and decision-making. Note that both data and information are the key assets in every digitally-enabled organisation. The term “Data” can be described as a collection of facts, figures, measurements, and signals. It is the first step on the road to “Knowledge”. Adding context to data leads us to information. More specifically, data and information give or equal to knowledge. That is exactly the step, where we are utilising the data to help to understand situations and make decisions. As said above, data is/are a collection, so, like every collection, it needs to be kept in somewhere safe, accessible, and unmanipulated. That is the reason why databases are needed. In terse, database is an accumulation of large collections of data at one central point. It is a collection of related data or facts. It is a self-describing collection of integrated facts or records to suit the organisational information needs. Database System(s) is a computerised approach for capturing, storing, and managing corporate data and information whereby the contents can be available, accessed, and shared on demand at anytime. It is a collection of application programmes with the ability to interact with the database via DBMS. A database application, also, is a computer programme that manages and controls access to the database via DBMS. An application programme is a computer system that interacts with users and the DBMS that manages and control access to the database. DBMS is a software system that enables experts and users to define, create, maintain, and control access to the database. It helps to create, modify, share, and manage database contents or transactions among various users and associated applications. The DBMS interfaces with application programmes, so that data stored in the database can be accessed by multiple applications and users. It provides user-friendly tools and interfaces to access and manipulate the data securely and efficiently. DBMS allows multiple users to interact with the database simultaneously while ensuring data consistency and integrity. The figure below shows database architecture of a sample company. Database design and mgt. Topic 1 by Dr. Ohene-Bonsu Simmons, Ph.D. Page 2 Cust. Service clerk Cust. Appl. Programme DBMS Order Proc. Clerk Database Cust. & Order Data Order Proc. Appl. Prg. 1.3 define what is meant by database design Database design is a process for describing data required to suit a particular organisation in terms of objects and their related components. It is a process that specifies the schema (table) of a database in a particular context or organisation. For instance, how can a user describe a real-world enterprise (e.g., a university) in terms of the data stored in a DBMS? What factors must be considered in deciding how to organise the stored data? The design consists of six (6) phases like the normal systems development lifecycle approach. The stages are requirements analysis, conceptual database design, logical database design, schema refinement, physical database design, and security design. Experts can also have a conceptual design to suit their needs, which can generate a high-level description of the data (see Topic 4). Experts always use three key terms in database design, namely; table, relationship, and domain. The entity- relationship (ER) data model allows experts to describe the data involved in a real-world enterprise in terms of objects and their relationships. This model is widely used to develop an initial database design. It provides a graphical approach in the design phase. Also, it allows experts to describe the data required for a particular organisation in terms of objects (subjects) and their relationships. 1.4 define what is meant by database management Database management is a collection of programmes that enable experts and users to create and maintain a database. In other words, it is a general-purpose software that provides the experts or users with the processes of defining, constructing, and manipulating the database for various applications. This means experts and users take care of organisational database to ensure that it works to meet the required tasks. This approach enables experts to be more strategic with organisational data and information assets. The tasks include generating students with outstanding balance, identifying voters less than 18years (minors), and generating lists of customers at a particular location. More specifically, management means measures or processes put in place to handle an organisational database. 1.5 identify the major/various categories of database Database consists of various types but with the same objective of storing large volume of corporate records. The major types consist of four (4), namely; relational, hierarchical, network, and object- oriented systems (see Topic 2). Database design and mgt. Topic 1 by Dr. Ohene-Bonsu Simmons, Ph.D. Page 3  1.6 identify database application areas (case areas or where applicable) Database has become the backbone of every organisation in this digital economy due to its ability to accumulate and share records for a decision-making. The application areas include the following: ✓ Universities: registration, payment, and course ✓ Banking: all transactions ✓ Airlines: reservations and schedules ✓ Sales: customers, orders, and payments ✓ E-commerce or Online retailers: user profiles, order tracking, and purchase history ✓ Human resources: employee records, salaries, and tax deductions ✓ Manufacturing: production, inventory, and orders The examples above show that database is widespread across the world for different projects. The application areas also provide an interoperability or a compatible platform to share data and information across the digital economy. Such as sharing or linking data and information via the following: ✓ A personal address book in email ✓ Profiles associated with social media platforms ✓ A website for capturing registered users The tools associated with databases for learning vary in different contexts ranging from visualisation to web-based applications or to serious games. Most databases, in this contemporary world, are associated with artificial intelligence (AI) techniques to ease the processing and management. The main objective of AI in databases is to improve the query process. 1.7 strength and weakness of database design and management Strengths of database are what make the DBMS superb than flat file systems. The strengths include the following: ✓ Sharing of data and multiuser systems. Contemporary database systems are designed for multiple users to manage data via online for concurrent activities. This approach permits many users across the world to access organisational database at the same time without a limit. ✓ Data independence – this strength associated with database isolates data activities without interfering with each other’s transactions. The approach is that data and information in the database are separated from the individual application programmes as they are controlled by various users concurrently. Thus, changes by users to the database are controlled by the DBMS and are not the various embedded application programmes. The weaknesses include the following: ✓ Databases are unable to perform innovative calculations like spreadsheet ✓ The systems may slow down when many users are accessing the same application programme concurrently. 1.8 preview of other topics The manual for this course consists of 8 topics (see the course outline) Database design and mgt. Topic 1 by Dr. Ohene-Bonsu Simmons, Ph.D. Page 4