Database Systems - Chapter 8

• Database Management Systems is explored in Chapter 8.
• The raw facts constitutes data which may be related to a person, place, activity, or thing.
• Information is the processed or organized form of data.
• A database is an organized collection of logically related data items which can be visualized as a container of information.
• Database Management System (DBMS) software is used to create, update, and retrieve data.
• Examples of DBMS software are MS Access, Open Office, LibreOffice Base, Oracle, Ingress, and MySQL.
• Data can be organized into two types: Flat File and Relational File.
• In a Flat File, data is stored in a single table, suitable for smaller amounts of data.
• In a Relational File, data is stored in multiple tables linked by a common field, suitable for medium to large amounts of data.
• Advantages of Database include organized storage, data analysis, data sharing, minimal data redundancy, data consistency, data efficiency, increased accuracy and validity, and data security.
• Disadvantages of Database are increased cost, high complexity and hardware cost, large size, upgradation and data conversion costs, increased staff cost, and the risk of database failure.
• A database can have one or more tables.
• Each table in a database contains information about one type of item.
• Each record in a table must be unique.
• Every database should have one or more fields designated as key(s).
• A data model is the structure of a database that describes how data will be stored and retrieved.
• Types of data models include Hierarchical, Network, and Relational.
• In a Hierarchical Data Model, data is stored in the form of records, and these records are linked to each other at various levels in a tree-like structure.
• In a Network Data Model multiple records are linked to the same master file creating an inverted tree structure.
• Relational Data Model sets relationships between two or more tables of the same database.
• Relational Data Model is the most commonly used database model.
• Relational Database Terminology includes Entity, Table, Field (or Column or Attributes), Data Values, Record (or Row), Primary Key, Relation Database, Foreign Key, Candidate Key, and Alternate Key.
• Keys in RDBMS data can be integrated using keys: Primary Key, Composite Primary Key, Foreign Key, Candidate Key, and Alternate Key.
• A Primary Key (PK) is a unique value that identifies a row in a table, and can be a single column or multiple columns.
• A Composite Primary Key is a primary key constraint applied on one or more columns.
• A composite key is made by combining two or more columns in a table to uniquely identify each row.
• A Foreign Key (FK) identifies a column or set of columns in one (referencing) table that refers to a column or set of columns in another (referenced) table.
• Candidate Keys are all the field values that are eligible to be the primary key for a table.
• An Alternate Key is the secondary candidate key that contains all the properties of a candidate key but is an alternate option.
• The attributes include Employee-ID, Aadhar Number, Employee Name, Job, Salary, Department Number etc
• Database Objects include tables, forms, queries, and reports.
• A Table is a set of data elements organized using a model of vertical columns and horizontal rows, and is the basic unit of any DBMS.
• Forms are features of a database that allow data entry in an easy and user-friendly manner.
• Queries are used to retrieve desired information from the database: basically questions asked from a database.
• Reports display the output of a query.
• Chapter 9 discusses getting started with LibreOffice Base.
• Data types are used to identify which type of data is going to be stored in the database.
• Fields can be of different types depending on the data they contain: Text, Numeric, Currency, Date, Boolean, Binary.

Text Data Type

• The text is a combination of letters, numbers, or special characters without arithmetic calculations.
• Examples include PAN Card Number, Name, Marks, etc.
• Memo (or LONGVARCHAR) stores descriptive data having more than 255 characters (up to 64,000 characters).
• Text (fix) or CHAR stores exactly the length specified by the user, such as license or passport numbers.
• Text or VARCHAR stores up to the specified length, allocating bytes based on entered characters.

Numeric Data Type

• The data consists of numbers (integer or real) on which arithmetic calculations can be performed.
• For example, 10, -34.8, 90.6789, -86 are of numeric data type.

Other Data Types

• Currency Data Type indicates monetary values in various currencies, e.g., $100, £500, or Rs. 25.50.
• Boolean Data Type has only two values: True or False (or Yes/No, On/Off).
• Binary Data Type stores digitized images and sounds as a long string of zeros and ones which enables storage of photos of products/employees or sound snippets in Base database.
• Date Data Type indicates dates and time, the dates and times can be stored in various formats, e.g., 12/25/2019, 08:45 AM.
  • Date stores the year, month, and day.
  • Time stores the hour, minute, and second.
  • Timestamp stores date and time information at once.
• User Interface of LibreOffice Base consists of the Title Bar, Menu Bar, Standard Toolbar, Status Bar, and Database Pane.
• The Title Bar displays the name of a database and its application, with window buttons located in the right corner.
• The Menu Bar appears below the title bar and includes File, Edit, View, Insert, Tools, Window, and Help that contain commands for various database operations.
• The Standard Toolbar is located below the menu bar and is used to access frequently used tools.
• The Status Bar is located at the bottom of the interface window and displays information about the type of view of the object in the database.
• The Database Pane is located on the left side of the window.
  • LibreOffice Base is the collection of related data objects known as Tables, Forms, Queries, Reports and application modules.

Table Creation and Sorting

• Tables, are basic elements of a database. Data is stored within tables. Tables can be created in Design View or by using the table creation Wizard.
• Components of Navigation Box includes Record Selector Box and Navigation Buttons.
• Sorting entails arranging data in a table in ascending or descending order.