The E-R model that is supported with the additional semantic concepts is called the extended entity relationship model. Specialization: It is the process of designation subgroupings within an entity set. It is a Top-down process. Example: Person – <IS A> – Employee – Customer Generalization: It is the process of defining a more general entity type from a set of more specialized entity types. Example: Person Condition defined constraint: In condition-defined lower-level entity sets, membership is evaluated on the basis of whether or not an entity satisfies an explicit condition or predicate. Example: Account_type = “checking account” User defined constraint: ...
Read more
Super key: Allows us to identify uniquely an entity in the entity set. Example: Roll_No attribute of student distinguishes student entity from another. Candidate key: A superkey for which no subset is a superkey. Example: Roll_No and {Student_Name, Student_street} are candidate keys Primary key: It is a candidate key that is chosen by the database designer as the principle means of identifying entities within an entity set. Example: Address field should not be a primary key whereas Social Security Number can be a primary key. Foreign key: An attribute or set of attributes, within one relation that matches the candidate ...
Read more
An E-R enterprise schema may define certain constraints to which the contents of a database system must conform. Mapping Cardinalities One to one One to many Many to one Many to many Participation Constraints Total participation: If every entity in E participation is at least one relationship in R. Partial participation: If only some entities in E participate in relationships in R.
Entity relationship model Keywords: Entity, Relationship, Attributes Advantages: Easy to develop, Mapping cardinalities, primary key, generalization and specialization Disadvantages: not use for implementation Relational model Keywords: tables, columns with unique names Advantages: Structural independence, Conceptual simplicity, Good for ad hoc requests, Simple to navigate, Greater flexibility Disadvantages: Significant hardware and software overheads, Not as good for modeling, Slower processing times Hierarchical model Keywords: tree structure, main frame database Advantages: High speed of access, ease of updates, simplicity, data security and efficiency Disadvantages: implementation complexity, database management problems, lack of structural independence Network model Keywords: based on directed graph theory, ability ...
Read more
A person who has central control over the system is called a Database Administrator (DBA). The functions of DBA include: Schema definition Schema and physical organization modification Granting of authorization for data access Routine Maintenance
A database system provides a data definition language to specify the database schema and a data manipulation language to express database queries and updates. DDL (Data Definition Language) We specify a database schema by a set of definitions expressed by a special language called a DDL. Create Alter Drop Truncate Comment Example: create table account (acc_no char(10), balance integer); DML (Data Manipulation Language) DML is retrieval, insertion, and deletion of information from database. Insert Update Delete Query Example: select acc_no, balance from account where acc_no = ‘A101’;
A major purpose of DBMS is to provide users with an abstract view of the data. There are 3 levels of abstraction. Physical Level: Describes how the data are actually stored (complex low-level data structures in detail) e.g. customer, account, employee record Logical View: Describes what data are stored in database and relationships. e.g. data type of data View Level: Describes only part of entire database. e.g. application programs to display data
Introduction Database is a collection of data. It contains information about one particular enterprise. DBMS is a set of prewritten programs that are used to store, update and retrieve a database. It accepts requests for data from the application program and instructs the operating system to transfer the appropriate data. Some Application Areas Banking and financial services Culture and scientific information Transport Tourism Library systems One can derive importance of DBMS from above points. Advantages of DBMS Redundancy can be reduced Inconsistency can be avoided The data can be shared Standards are enforced Security can be enforced Integrity can be ...
Read more
Joins tables Many forms: INNER JOIN (include only matching columns) OUTER JOIN (include all columns) LEFT OUTER JOIN NATURAL JOIN CONDITION JOIN “JOIN” means “INNER JOIN” in MySql. Example of a Condition Join Statement: JOIN the CountryLanguage and Language tables using the country code SELECT CO.Name, L.language, L.percentage FROM Country CO JOIN CountryLanguage L ON CO.code = L.countrycode WHERE ...; Example of Multiple Table Join SELECT CO.name, C.*, L.language FROM Country CO JOIN CountryLanguage L ON CO.code = L.countrycode JOIN City C ON CO.code = C.countrycode WHERE ...; /* more conditions */
Source: http://www.slideshare.net/ronwarshawsky/rw-nosql-2013presentatonv1 OS Tuning : Try “tuned” – best when used in staging/load testing environment under realistic load. Storage Tuning : RAID 10, Ext4 or XFS, Delaysharding with better I/O, SSD + FlashCache – shutterflytested Database Tuning : Database configuration Option to disable services Database monitoring tools Database network monitoring tools Sharding / Replicating Load Testing : Why load test? validate upgrades validate fixes validate platform and hardware changes validate multiples of production loads Options for load testing Benchmark load testing Disk I/O load testing Real traffic load testing