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:
NOT NULL - Indicates that a column cannot store NULL value UNIQUE - Ensures that each row for a column must have a unique value PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Ensures that a column (or combination of two or more columns) have a unique identity which helps to find a particular record in a table more easily and quickly FOREIGN KEY - Ensure the referential integrity of the data in one table to match values in another table CHECK - Ensures that the value in a column meets a specific condition DEFAULT - Specifies a default value for a column E-R Diagram : An entity-relationship diagram (ERD) is a data modeling technique that graphically illustrates an information system‘s entities and the relationships between those entities. An ERD is a conceptual and representational model of data used to represent the entity framework infrastructure. Following components are used to create ERD: Rectangles, which represent entity sets. Ellipses, which represent attributes. Diamonds which represent relationships among entity sets. Lines, which links attributes to entity sets and entity set to relationship. If the attributes are composite, they are further divided in a tree like structure. Every node is then connected to its attribute. That is, composite attributes are represented by ellipses that are connected with an ellipse. Multivalued attributes are depicted by double ellipse. Derived attributes are depicted by dashed ellipse. Complex Attribute: A complex attribute is an attribute that is both composite and multivalued. Fig: Composite attribute Fig:Multivalued attribute Fig: Derived attribute Steps involved in creating an ERD include: Identifying and defining the entities Determining all interactions between the entities Analyzing the nature of interactions/determining the cardinality of the relationships Creating the ERD Binary Relationship and Cardinality in ERD A relationship where two entities are participating is called a binary relationship. Cardinality is the number of instance of an entity from a relation that can be associated with the relation.