DATABASE MANAGEMENT SYSTEM
A database is a collection of data that is organised so that its contents can easily be accessed,managed and updated.
Database Management system(DBMS)
DBMS is a collection of programs that enables users to create and maintain a database. Ex: LibreOffice BASE,MS Access,Oracle,SQL server etc.
Desktop Database and Server Database
Desktop database are designed for single user. It is stored in a single system while Server Database are Multi user applications. The database are stored in server and users can operate the database with connected systems.
Database: Backend and front-end files
The interface through which the user interacts with the software is termed as Front end and the database which is kept on a server is termed as back end
LibreOffice Base and its Components
Open LibreOffice Base: Applications –> Office –> LibreOffice Base
LibreOffice Base is the popularly used DBMS to create ,store and manage database.
Relational Database Management System(RDBMS)
LibreOffice Base is a RDBMS.In a relational Database , the Tables are related on common fields known as Key fields. It helps in accessing data from more than one table. Four components used in RDBMS
1-Entities:- Any thing in the real world with Independent existence is called as an entity.Ex: Person,place,thing, account, voucher etc.In LIbreOffice base entities are represented through Tables.
2-Attributes:– These are additional characters that further describe an entity.Ex: DOB,Height, Voucher No etc. Attributes are represented by Columns(fields) in a table. Attributes may be
- Simple Attribute :– it cannot be further subdivided. Ex : height
- Composite Attribute : it can be further subdivided Ex:Name of a person , can be subdivided in to first name, middle name last name etc
- Single valued :– An attribute with a single value for an entity is single valued attribute.Ex: Height
- Multi Valued attributes : It has multiple values .Ex:-Qualifications
- Stored Attributes :-Attributes which are stored as independent ex: Date of Birth
- Derived Attributes:- Attributes becomes dependent to stored attributes ex: Age
- Complex Attributes : The composite or multi valued attributes may be grouped to constitute a complex attribute.
- Null values:– Absence of a data item is represented by a special value called null value.
3-Identifier(Key attribute of an entity type)
An attribute which contains unique values for identifying the entity instances is known as identifier or key attribute or Primary key.
A relationship is a logical linkage between two entities that describe how the entities are associated with each other.
Types of relationship
- One to One
- One to many
- Many to many
Entity Types :-Collection of entities is called Entity types. Ex: customers,employees,Vouchers etc
Entity Instance :- The values of attributes of an entity. Ex: Details of Employee like ID No.,Name,Age, Designation.
Entity Set:- Collection of all entity instances. Ex: All employees details in an organisation.
Primary key and Foreign Key
Primary key is a special column in the Table that can be used to identify any one row.The value in the primary key column must be unique in the table in which it resides
when we distribute data in many tables the main table is known as master table and other one is transaction table.The tables are related ,must be linked with a common field.The common key field in the master table in known as Primary key and the same in transaction table is known as Foreign key.
Elements of Database(Database objects)
- Table : In DBMS ,data is organised in tables.A table is a collection of data about a specific topic.Tables organise data in to columns(field) and rows(records)
Table can be created in three ways:-
- Create Table in design view
- Use wizard to create table
- Create view
Creating Tables in LibreOffice Base: Click on Tables – Create table in design view –Enter the Field name,Field type ,description click on Save button after setting the Primary key.
Field Name : It refers to column name of the table being created
Field Types (Data types) : LibreOffice Base support different data types,they are
- Text[VARCHAR] :- It is used to store text and numbers that are not to be used in any arithmetic calculations. Ex: Name, ID No, Telephone No.
- Memo[LONGVARCHAR] :-It is used to store large amount of alphanumeric information such as notes,comments,description or address field.
- Number [NUMERIC]:- It is meant to store numbers can be used for mathematical calculations.
- Date [DATE]:- Used to store date in to database table
- Time [TIME]:-It is used to store time only.
- Date/Time [TIMESTAMP]:- It is used to store combination of both date and time
- Yes/No[BOOLEAN] :- Yes and No values and fields that contains only one of two values.
- Image [LONG]VARBINARY
- Other data types:- can store different numeric types,images etc.
- Forms : Forms are screens that allow viewing ,adding and updating the data stored in the table.
Forms can be created :-
- Create Table in design view
- Use wizard to create table
Click on Forms under Database – Use Wizard to create form– Select the table or query to add the fields into the form – Select appropriate options from the coming windows – Finish.
- Queries : It may be defined as a set of Instructions to retrieve certain information from the database.
Three methods for creating Queries:
- Create query in design view
- use wizard to create query
- Create query in sql view
Creating Queries – Click on query icon on the left panel – Use Wizard to create query –select appropriate options from the coming screen ,then click finish.
- Reports : Report used to create various reports based on queries and tables or both.
Reports can be created :
- Create report in design view
- Use wizard to create report
Click on the Report from the database panel – Create report by using wizard – Choose appropriate options from the coming windows – Give a suitable title for the report and save.