Database system are complex, difficult, and time consuming to design. Qualified personnel. Extensive conversion costs in moving from a file system to a database system. Initial training required for all programmers and users. Database Applications – DBMS Applications where we use Database Management Systems are: Telecom: There is a database to keeps track of the information regarding calls made, network usage, customer details etc. Without the database systems it is hard to maintain that huge amount of data that keeps updating every millisecond. Industry: Where it is a manufacturing unit, warehouse or distribution centre, each one needs a database to keep the records of ins and outs. For example distribution centre should keep a track of the product units that supplied into the centre as well as the products that got delivered out from the distribution centre on each day; this is where DBMS comes into picture. Banking System: For storing customer info, tracking day to day credit and debit transactions, generating bank statements etc. All this work has been done with the help of Database management systems. Education sector: Database systems are frequently used in schools and colleges to store and retrieve the data regarding student details, staff details, course details, exam details, payroll data, attendance details, fees details etc. There is a hell lot amount of inter-related data that needs to be stored and retrieved in an efficient manner. Online shopping: You must be aware of the online shopping websites such as Amazon, Flipkart etc. These sites store the product information, your addresses and preferences, credit details and provide you the relevant list of products based on your query. All this involves a Database management system. DBMS Evolution/history of DBMS : i) 1960’s(Traditional File System) : In 1960‟s the traditional file system was invented. The traditional file based system is basically file based system in which we manually or through computer handle the database. ii) Early 1970‟s(Tree Structure Model) : The emergence of the first types of DBMS, the hierarchical DBMS. IBM had the first model developed on IBM 360, and their DBMS was called IMS, originally it was written for the Apollo program. This types of DBMS was based in binary trees, where the shape was like a tree and relations were only limited between parent and child record. Advantage : Less redundant data Data independence Security & integrity Efficient searching Disadvantages : Complex implementation Harder to handle many relationship iii)Late 1970‟s (Network Data Model): The emergence of the network DBMS, charles Bachman developed first DBMS at Honeywell database named Integrated data store (IDS). A group called COD ASYL who is responsible for the creation of COBOL and that system standardized network DBMS where we developed for business use. In this model, each record can have multiple parents . Disadvantage :- Complex to understand - Difficult to design & maintenance iv) 1980‟s(Relational Data Model): The emergence of relational DBMS on the hands of Edgar codd . He worked at IBM and he was invented relational data model. This was a new system for entering data and working with big database where the idea was use a table of records. v) 1990‟s & onwards(Object Oriented Model) : In 1990 the DBMS took on a new objects oriented approach, joint with relational DBMS. In This approach, text multimedia internet and web use in conjunction with DBMS were available and possible. Data Models Data models define how the logical structure of a database is modeled. Data Models are fundamental entities to introduce abstraction in a DBMS. Data models define how data is connected to each other and how they are processed and stored inside the system.The very first data model could be flat data-models, where all the data used are to be kept in the same plane.
Types of Data Models: Object based logical Models – Describe data at the conceptual and view levels. E-R Model Object oriented Model Record based logical Models: These models specify logical structure of database with records, fields and attributes. Relational Model Hierarchical Model Network Model schema /schema diagram: schema of a database system is it‟s structure described in a formal language supported by DBMS and refers to the organization of data to create blue print of how database will be constructed . -Internal level : Internal level has an internal schema which describe the physical storage structure of the database. The internal schema uses a physical data model and describes the complete details of data storage and access path for the database. - Conceptual level : Conceptual level has a conceptual schema, which describe the structure of the whole database for a community of users. The conceptual schema hides the details of physical storage structure and concentrates on describing entities, data types, relationship users operation & constraints. - External level :External level includes a number of external schema or user view. Each external schema describes a part of the database that a particular user group is interested in and hides that rest of the database from that user group. # Instance:Actual data in the database may change quite frequently. For Example, the database change every time we add a student or enter the new grade .The data in the database at a particular moment in time is called a database State or snap shot . It is also called the current set of occurrences or instances in the database. # View:- View is a single table that is described from other tables. These other tables can be base tables or previously defined views. A view doesn‟t necessarily exist in physical form, it is considered a virtual table. Advantages of views:Ø View can represent a subset of the data contained in a table. Ø Views can join multiple table into a single virtual table. Ø View can hide the complexity of data. Ø View take very little space to store. Database administrator (DBA) DBA is a person responsible for the installation, configuration, upgrade , monitoring and maintenances of physical database. The role includes the developing and design of database strategies, monitoring database performances capacity and planning for future expansible requirement. They may also plan, co- ordinate and implement security measure to safeguard of the database. Database Manager (DB Manager) A database manager (DB manager) is a computer program, or a set of computer programs, that provide basic database management functionalities including creation and maintenance of databases. Database managers have several capabilities including the ability to back up and restore, attach and detach, create, clone, delete and rename the databases.
Database Users Database administrators – DBA is responsible for authorizing access to the database, for coordinating and monitoring its use, and acquiring software and hardware resources as needed. Database designers – identify data to be stored in the database and choosing appropriate structures to represent and store the data. Most of these functions are done before the database is implemented and populated with the data. It is the responsibility of the database designers to communicate with all prospective users to understand their requirements and come up with a design that meets these requirements. End Users : Casual End Users – occasionally access, may need different information each time. Use query language to specify requests. Naïve or parametric end users – main job is to query and update the database using standard queries and updates. Sophisticated end users – engineers, scientists, analysts who implement applications to meet their requirements. Stand alone users – maintain personal databases using ready made packages. Applications Architecture An applications architecture describes the behaviour of applications used in a business, focused on how they interact with each other and with users. It is focused on the data consumed and produced by applications rather than their internal structure. The applications architecture is specified on the basis of business and functional requirements. This involves defining the interaction between application packages, databases, and middleware systems in terms of functional coverage. 1-Tier Architecture 1-Tier Architecture is the simplest, single tier on single user, and is the equivalent of running an application on a personal computer. All the required component to run the application are located within it. User interface, business logic, and data storage are all located on the same machine. They are the easiest to design, but the least scalable. Because they are not part of a network, they are useless for designing web applications. 2-Tier Architectures 2-Tier Architectures supply a basic network between a client and a server. For example, the basic web model is a 2Tier Architecture. A web browser makes a request from a web server, which then processes the request and returns the desired response, in this case, web pages. This approach improves scalability and divides the user interface from the data layers. However, it does not divide application layers so they can be utilized separately. This makes them difficult to update and not specialized. The entire application must be updated because layers aren‘t separated. 3- n-tier Application An n-tier application program is one that is distributed among three or more separate computers in a distributed network. The most common form of n-tier (meaning 'some number of tiers') is the 3-tier application, in which user interface programming is in the user's computer, business logic is in a more centralized computer, and needed data is in a computer that manages a database. Database design and the e-r model UNIT - 2 Database Design and the E-R model Entity relationship model : an entity–relationship model (ER model) is a data model for describing the data or information aspects of a business domain or its process requirements, in an abstract way that lends itself to ultimately being implemented in a database such as a relational database. The main components of ER models are entities (things) and the relationships that can exist among them, and databases. Fig: e-r model Entity An entity can be a real-world object, either animate or inanimate, that can be easily identifiable. For example, in a school database, students, teachers, classes, and courses offered can be considered as entities. All these entities have some attributes or properties that give them their identity.
Entity Set An entity set is a collection of similar types of entities. An entity set may contain entities with attribute sharing similar values. For example, a Students set may contain all the students of a school; likewise a Teachers set may contain all the teachers of a school from all faculties. Entity sets need not be disjoint. Relationship The association among entities is called a relationship. For example, an employee works_at a department, a student enrolls in a course. Here, Works_at and Enrolls are called relationships. Relationship Set A set of relationships of similar type is called a relationship set. Like entities, a relationship too can have attributes. These attributes are called descriptive attributes. Attributes Entities are represented by means of their properties, called attributes. All attributes have values. For example, a student entity may have name, class, and age as attributes. There exists a domain or range of values that can be assigned to attributes. For example, a student's name cannot be a numeric value. It has to be alphabetic. A student's age cannot be negative, etc. Mapping Cardinalities Cardinality defines the number of entities in one entity set, which can be associated with the number of entities of other set via relationship set. One-to-one − One entity from entity set A can be associated with at most one entity of entity set B and vice versa. Fig: One to one relationship One-to-many − One entity from entity set A can be associated with more than one entities of entity set B however an entity from entity set B, can be associated with at most one entity. Fig: one to many relationship Many-to-one − More than one entities from entity set A can be associated with at most one entity of entity set B, however an entity from entity set B can be associated with more than one entity from entity set A. Fig: Many to one relationship Many-to-many − One entity from A can be associated with more than one entity from B and vice versa. Fig: Many to many relationship SQL Constraints SQL constraints are used to specify rules for the data in a table. If there is any violation between the constraint and the data action, the action is aborted by the constraint. Constraints can be specified when the table is created (inside the CREATE TABLE statement) or after the table is created In SQL, we have the following constraints: