![ENTITY & ATTRIBUTES Composite vs SimpleSingle vs MultivaluedStored vs DerivedComplex Attributes Attributes that can be represented by grouping composite attributes between (), separating the components with commas, and by displaying the multivalued attributes between [] are known as complex attributes. {Address_phone({Phone(Area_code,Phone_number)},Address(Street_addre ss{Number,Street, flat_number),City,State,Zip))} www.edureka.co](https://image.slidesharecdn.com/postgresqltutorialforbeginners-191024104631/75/PostgreSQL-Tutorial-For-Beginners-Edureka-22-2048.jpg)
Uploaded byEdureka!
PostgreSQL Tutorial For Beginners | Edureka
The document discusses various types of Database Management Systems (DBMS), particularly focusing on SQL and PostgreSQL, an advanced open-source relational database. It explains SQL commands, attributes, normalization forms, and different facets of database constraints and joins. Key topics include transaction control, data integrity, types of attributes, and the organization of database tables to minimize redundancy.
PostgreSQL Tutorial For Beginners | Edureka
- 2. TYPES OF DBMS WHATIS SQL? WHAT IS POSTGRESQL? FEATURES OF POSTGRESQL SQL COMMAND CATEGORIES INSTALL POSTGRESQL www.edureka.co
- 3.
- 4.
- 5.
- 6. A standardized programming language whichis used for managing relational databases. With SQL, you can modify databases, add, update or delete rows of data, retrieve subsets of information from a database and ,any more Relational databases like MySQL Databases, oracle, Ms SQL Server, Sybase etc use SQL. Queries and other SQL operations are written as statements. Example: select, insert, add, update, delete, create, alter, truncate. www.edureka.co
- 7.
- 8. PostgreSQLis an open-sourceobject relational database system with a 30+ years of active development in the industry. World’s Most Advanced Open Source Relational Database www.edureka.co
- 9.
- 10. Data Types DataIntegrity Performance Reliability Security Extensibility www.edureka.co
- 11.
- 12.
- 13. . Transaction Control Language(TCL) COMMIT, ROLLBACK, SAVEPOINT DataControl Language(DCL) GRANT, INVOKE Data Manipulation Language(DML) SELECT, INSERT, UPDATE, DELETE CREATE, DROP, ALTER Data Definition Language (DDL) Includes the commands which mainly deal with the transaction of database. Includes commands which mainly deals with the rights, permissions and other controls of the database system. The SQL commands that deals with the manipulation of data present in database. Consists of the commands that can be used to define the database schema. www.edureka.co
- 14.
- 15.
- 16.
- 17.
- 18. Composite vs Simple Singlevs Multivalued Stored vs Derived Complex Attributes ENTITY & ATTRIBUTES www.edureka.co
- 19. ENTITY & ATTRIBUTES Composite vsSimple A composite attribute can be divided into smaller subparts. These subparts represent individual basic attributes with their own meanings. Attributes which are not divisible are simply known as simple or atomic attributes. Address Street _address City State Zip Number Street House_number www.edureka.co
- 20. ENTITY & ATTRIBUTES Composite vsSimpleSingle vs Multivalued Attributes having single value for a particular entity are known as single- valued attributes. A multivalued attribute may have lower and upper bounds to constrain the number of values allowed for each individual entity. www.edureka.co
- 21. ENTITY & ATTRIBUTES Composite vsSimpleSingle vs MultivaluedStored vs Derived Attributes which are derived from the real entities are known as derived attributes. The stored attribute are such attributes which are already stored in the database and from which the value of another attribute is derived is called stored attribute. Age Birth_date www.edureka.co
- 22. ENTITY & ATTRIBUTES Composite vsSimpleSingle vs MultivaluedStored vs DerivedComplex Attributes Attributes that can be represented by grouping composite attributes between (), separating the components with commas, and by displaying the multivalued attributes between [] are known as complex attributes. {Address_phone({Phone(Area_code,Phone_number)},Address(Street_addre ss{Number,Street, flat_number),City,State,Zip))} www.edureka.co
- 23.
- 24.
- 25.
- 26. NOT NULL UNIQUECHECK DEFAULT INDEX www.edureka.co Ensures that a NULL value cannot be stored in a column This constraint makes sure that all the values in a column are different This constraint ensures that all the values in a column satisfy a specific condition. This constraint consists of a set of default values for a column when no value is specified. This constraint is used to create and retrieve data from the database very quickly. www.edureka.co CONSTRAINTS IN DATABASE
- 27.
- 28. A technique thatorganizes tables in such a way that redundancy and dependency of data is reduced. BCNF 3 NF 2 NF 1 NF www.edureka.co
- 29. Salutation Full NameAddress Movies Rented Category Ms. Alice Johnson 1st Street House No 3 Mission Impossible: Fallout, Clash of Titans Action, Action Mr. David Allen 3rd Street 45 Interstellar, Edge Of Tomorrow Sci-Fi, Sci-Fi Mr. David Allen 7th Avenue Mission Impossible: Fallout Action www.edureka.co
- 30. NORMALIZATION First Normal Form 2NF 3 NF BCNF Each table cell should have a single value. So, basically all the records must be unique. Salutation Full Name Address Movies Rented Ms. Alice Johnson 1st Street House No 3 Mission Impossible: Fallout Ms. Alice Johnson 1st Street House No 3 Clash of Titans Mr. David Allen 3rd Street 45 Interstellar Mr. David Allen 3rd Street 45 Edge Of Tomorrow Mr. David Allen 7th Avenue Mission Impossible: Fallout www.edureka.co
- 31. NORMALIZATION 3 NF BCNF Database shouldbe 1NF and should also have single column primary key. 1 NF Second Normal Form Salutation Full Name Address Movies_Rented ID Salutation Full Name Address 1 Ms Alice Johnson 1st Street House No 3 2 Mr David Allen 3rd Street 45 3 Mr David Allen 7th Avenue ID Movies_Rented 1 Mission Impossible: Fallout 1 Clash of Titans 2 Interstellar 2 Edge Of Tomorrow 3 Mission Impossible: Fallout Table 1 Table 2 www.edureka.co
- 32. NORMALIZATION BCNF The database shouldbe in 2NF and must not have any transitive functional dependencies. 1 NF 2 NF Third Normal Form ID Full Name Address Salutation_ID ID Movies_Rented Salutation_ID Salutation 1 Mr. 2 Ms. 3 Mrs. 4 Dr. Table 1 Table 2 Table 3 www.edureka.co
- 33. NORMALIZATION 1 NF 2 NF 3NF Boyce-Codd Normal Form If your database is in 3rd Normal Form, there would be some scenarios where anomalies would be present, if you have more than candidate key. Then BCNF comes into role, where you divide your tables further so that there would be only one candidate key present. www.edureka.co
- 34.
- 35.
- 36. ARITHMETIC OPERATORS +, -, *,/, % BITWISE OPERATORS &, |, ^ COMPARISON OPERATORS ==, <, >, <=, >= <> COMPOUND OPERATORS +=, *=, -=, /=,%=, &=, ^-=, |*= www.edureka.co
- 37.
- 38. Nested queries areSQL queries which have an outer query and inner subquery. The subquery is a query nested within another query such as SELECT, INSERT, UPDATE or DELETE. SELECT lastname, firstname FROM employees WHERE addressCode IN (SELECT addressCode FROM office WHERE country = “INDIA”) OUTER QUERY SUBQUERY OR INNER QUERY www.edureka.co
- 39.
- 40. UNION Combines rows fromboth the queries INTERSECT Keeps only those rows which are common in both the queries. MINUS Keeps rows from the left query which are not included in the right query LEFT QUERY RIGHT QUERY FINAL RESULT www.edureka.co
- 41. Copyright © 2017,edureka and/or its affiliates. All rights reserved. www.edureka.co
- 42.
- 43.
- 44.
- 45.
- 46. JOINS in SQLare commands which are used to combine rows from two or more tables, based on a related column between those tables. They are predominantly used when a user is trying to extract data from tables which have one-to-many or many-to-many relationships between them. What are Joins? www.edureka.co
- 47. TYPES OF JOINS INNER JOIN FULLJOIN LEFT JOIN RIGHT JOIN www.edureka.co
- 48.
- 49. A view inSQL is a single table, which is derived from other tables Query ROWS VIEWS COLUMNS www.edureka.co
- 50.
- 51.
- 52.
- 53.
- 54.
- 55.
- 56.