Understanding Databases and SQL: A Comprehensive Overview
Databases are fundamental in computer-based record-keeping systems, essential for organizations to maintain and access crucial information efficiently. This comprehensive guide covers the significance of databases, advantages of centralized databases, database models, relational databases, and more.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
E N D
Presentation Transcript
Java Database 1. Pengantar Database 2. Pengantar SQL
Database Sistem Database adalah komputer berbasis sistem pencatatan. Sebuah sistem yang keseluruhan adalah memelihara informasi yang dianggap penting bagi organisasi. Basis Data merupakan operasional yang digunakan, dishare oleh berbagai aplikasi dan pengguna dari setiap organisasi. tujuannya untuk secara dan merekam kumpulan tersimpan, data dapat
Why Database? Sistem dengan kontrol terpusat dari data operasional, dan termasuk salah satu aset yang paling berharga. Ini benar-benar kebalikan dari situasi yang terjadi di banyak organisasi, dimana biasanya setiap aplikasi memiliki file pribadi sendiri (flat file). Hal ini membuat tersebar luas dan sulit dikendalikan. database menyediakan organisasi data operasional
Advantage of Centralized Database Redundansi dapat dikurangi. Inkonsistensi dapat dihindari. Data dapat dibagi. Standar dapat ditegakkan. Pembatasan keamanan dapat diterapkan. Integritas dapat dipertahankan. Persyaratan yang saling bertentangan dapat diseimbangkan.
Disadvantage of Database Systems Database lebih rentan terhadap kerusakan melalui: kerusakan mesin. kesalahan pribadi. gangguan manusia yang disengaja. Biaya: biaya perangkat pengembangan DB, dan pemeliharaan Db yang mahal. Kompleksitas: Karena kompleksitasnya, pengguna harus memahami itu menggunakannya secara efisien dan efektif. keras yang diperlukan, cukup baik untuk
Database Models - Product - Vendor MODEL 1. Relational PRODUCT DB2 Ingress Oracle Access PostgreSQL MySQL DMS100 IDMS IMS System 2000 Starburst Gemstone Orion VENDOR IBMSQL/DS Relational Tech. Oracle corp Microsoft 2. Network Unysis Cullinet IBM Intel IBM 3. Heirarchical 4. Object oriented
Relational Database Relational database adalah kumpulan dari table. Secara formal tabel disebut relasi. Database adalah menyimpan informasi tentang tabel, baris, dan kolom. struktur yang dapat
Relational Database Relational Model Relational Traditional DBMS File System Relation Tuple Attribute Primary Key (PK) Primary Key (PK) Relationship (FK) Relationship (FK) Table Row Column File Record Field Search Key Not Used
Relational Database 1. Primary Key (PK): Sebuah atribut yang unik dapat mengidentifikasi setiap record (tuple) dari relasi (tabel). 2. Foreign Key (FK): Sebuah atribut yang merupakan atribut table tapi primary key dalam tabel lain. biasa dalam satu
Example of a Relational Database Relation Name Attribute Primary Key (PK) Sale SalesNO Name 10 James 10 Dallas A211 12 Black 15 Denver F654 48 Black 8 WashDC A211 Rate City Dept# Tuple (record)
Example of a Relational Database Customer CustID Name Balance 132 Black 2000.00 135 Tom 129.89 Denver 12 198 Tom (132.90) Dallas 10 City SaleNo Dallas 10 SalesNO is PK in Sales table Sales SalesNO Name 10 James 10 Dallas A211 12 Black 15 Denver F654 48 Black 8 WashDC A211 Rate City Dept#
Example of a Relational Database Customer CustID Name Balance 132 Black 2000.00 135 Tom 129.89 Denver 12 198 Tom (132.90) City SaleNo Dallas 10 Dallas 10 SalesNO is PK in Sales table and FK in Customer table Sales SalesNO Name 10 James 10 Dallas A211 12 Black 15 Denver F654 48 Black 8 WashDC A211 Rate City Dept#
Example: Order Entry Database Order OrderLine ONO DATE 102 11/2/94132 10 199 2/15/95 135 12 92 10/4/94 102 53 CustID SalesNO ONO Oline# Part# Qty 102 112.00 10 EX454 102 2 129.89 1 DE012 199 1 32.90 3 DC810 Part# Customer CustID Name Balance 132 Black 2000.00 Dallas 10 135 Tom 129.89 Denver 12 198 Tom (132.90) Dallas 10 City SaleNo Sales SalesNO Name 10 James 10 Dallas A211 12 Black 15 Denver F654 48 Black 8 Rate City Dept# WashDC A211
Functionality of a DBMS Programmer komponen: Data Definition Language (DDL) Data Manipulation Language (DML) melihat SQL, yang memiliki dua Di belakang layar DBMS memiliki: Query Engine. Query Optimizer. Storage manajement. Transaction Manajemen (concurrency, recovery).
How the Programmer Sees the DBMS . 1 Mulailah dengan DDL untuk membuat tabel: CREATE TABLE Students ( Name CHAR(30) SSN CHAR(9) PRIMARY KEY NOT NULL, Category CHAR(20) ) . . . . 2 Lanjutkan dengan DML untuk mengisi tabel: INSERT INTO Students VALUES( Charles , 123456789 , undergraduate ) . . . .
Transactions Enroll Mary Johnson dalam CSE444 : BEGIN TRANSACTION; INSERT INTO Takes SELECT Students.SSN, Courses.CID FROM Students, Courses WHERE Students.name = Mary Johnson and Courses.name = CSE444 -- More updates here.... IF everything-went-OK THEN COMMIT; ELSE ROLLBACK Jika sistem crash, transaksi masih dilakukan atau dibatalkan.
Transactions Transaksi = urutan pernyataan yang baik semua berhasil, atau semua gagal. Transaksi memiliki sifat ACID: A = atomicity (transaksi harus dilakukan atau dibatalkan sama sekali). C = konsistensi (transaksi harus mengubah sistem dari satu negara yang konsisten ke kondisi konsisten lain). I = isolasi (setiap transaksi harus terjadi secara independen dari transaksi lainnya). D = daya tahan (transaksi selesai harus tetap permanen).
Queries Cari semua program yang "Mary" ambil. SELECT C.name FROM WHERE S.name= Mary and S.ssn = T.ssn and T.cid = C.cid Students S, Takes T, Courses C Apa yang terjadi di belakang layar? Prosesor Query angka keluar bagaimana untuk menjawab pertanyaan secara efisien.
Queries, Behind the Scene Declarative SQL query Imperative query execution plan: sname SELECT C.name FROMStudents S, Takes T, Courses C WHERE S.name= Mary and S.ssn = T.ssn and T.cid = C.cid cid=cid sid=sid name= Mary Courses Takes Students Optimizer memilih rencana eksekusi yang terbaik untuk query.
SQL Introduction Bahasa standar untuk query dan memanipulasi data. SQL = Structured Query Language Banyak standar di luar sana: ANSI SQL SQL92 (SQL2 a.k.a.) SQL99 (SQL3 a.k.a.) Vendor mendukung berbagai himpunan ini. Apa yang kita bahas adalah umum untuk semua dari mereka.
SQL Data Definition Language (DDL) Create/alter/delete tables and their attributes Data Manipulation Language (DML) Query one or more tables Insert/delete/modify tuples in tables Transact-SQL Idea: package a sequence of SQL statements server
Data Types in SQL Characters: CHAR(20) VARCHAR(40) Numbers: BIGINT, INT, SMALLINT, TINYINT REAL, FLOAT MONEY Times and dates: DATE DATETIME Others... All are simple -- fixed length -- variable length -- differ in precision -- SQL Server
SQL Data Type vs Java Data Type SQL Data Type Java Data Type INTEGER or INT int REAL float DOUBLE double Fixed-point decimal numbers with m total digits and n digits after the decimal point; similar to BigDecimal. DECIMAL(m, n) BOOLEAN Boolean VARCHAR(n) Variable-length String of length up to n CHARACTER(n) or CHAR(n) Fixed-length String of length n
Tables in SQL Attribute names Table name Product PName Price Category Manufacturer Gizmo $19.99 Gadgets GizmoWorks Powergizmo $29.99 Gadgets GizmoWorks SingleTouch $149.99 Photography Canon MultiTouch $203.99 Household Hitachi Tuples or rows
Tables Explained Sebuah tuple = rekor Pembatasan: semua atribut adalah jenis atom Sebuah tabel = satu set tupel Seperti daftar ... ... Tapi itu unorderd: no first(), tidak ada next(), tidak ada last(). Tidak nested tables, hanya table datar diperbolehkan!
Tables Explained Skema tabel adalah nama tabel dan atributnya: Produk (PName, Harga, Kategori, Manfacturer) Sebuah kunci adalah atribut yang nilai-nilai yang unik,? Kami menggarisbawahi kunci Produk (PName, Harga, Kategori, Manfacturer)
SQL Query Basic form: (plus many many more bells and whistles) SELECT attributes FROM relations (possibly multiple, joined) WHERE conditions (selections)
Simple SQL Query Product PName Price Category Manufacturer Gizmo $19.99 Gadgets GizmoWorks Powergizmo $29.99 Gadgets GizmoWorks SingleTouch $149.99 Photography Canon MultiTouch $203.99 Household Hitachi SELECT * FROM Product WHERE category= Gadgets PName Price Category Manufacturer Gizmo $19.99 Gadgets GizmoWorks Powergizmo $29.99 Gadgets GizmoWorks selection
Simple SQL Query Product PName Price Category Manufacturer Gizmo $19.99 Gadgets GizmoWorks Powergizmo $29.99 Gadgets GizmoWorks SingleTouch $149.99 Photography Canon MultiTouch $203.99 Household Hitachi SELECT PName, Price, Manufacturer FROM Product WHERE Price > 100 PName Price Manufacturer selection and projection SingleTouch $149.99 Canon MultiTouch $203.99 Hitachi
A Notation for SQL Queries Input Schema Product(PName, Price, Category, Manfacturer) SELECT PName, Price, Manufacturer FROM Product WHERE Price > 100 Answer(PName, Price, Manfacturer) Output Schema
Selections Apa yang terjadi dalam klausa WHERE: x = y, x <y, x <= y, dll Untuk jumlah, mereka memiliki arti yang biasa Untuk CHAR dan VARCHAR: leksikografis pemesanan Konversi diharapkan antara CHAR dan VARCHAR Untuk tanggal dan waktu, apa yang Anda harapkan ... Pencocokan pola pada string: LIKE
The LIKE operator LIKE : pattern matching on strings p may contain two special symbols: % = any sequence of characters _ = any single character Product(Name, Price, Category, Manufacturer) Find all products whose name mentions gizmo : SELECT * FROM Products WHERE PName LIKE %gizmo%
Eliminating Duplicates Category Gadgets SELECT category FROM Product Gadgets Photography Household Compare to: Category SELECT DISTINCT category FROM Product Gadgets Photography Household
Ordering the Results SELECT pname, price, manufacturer FROM Product WHERE category= gizmo AND price > 50 ORDER BY price, pname Ordering is ascending, unless you specify the DESC keyword. Ties are broken by the second attribute on the ORDER BY list, etc.
Ordering the Results SELECT Category FROM Product ORDER BY PName PName Price Category Manufacturer ? Gizmo $19.99 Gadgets GizmoWorks Powergizmo $29.99 Gadgets GizmoWorks SingleTouch $149.99 Photography Canon MultiTouch $203.99 Household Hitachi
Ordering the Results Category SELECT DISTINCT category FROM Product ORDER BY category Gadgets Household Photography Compare to: ? SELECT DISTINCT category FROM Product ORDER BY PName
Joins in SQL Connect two or more tables: PName Gizmo Powergizmo SingleTouch MultiTouch Price $19.99 $29.99 $149.99 $203.99 Category Gadgets Gadgets Photography Household Manufacturer GizmoWorks GizmoWorks Canon Hitachi Product Company CName StockPrice Country GizmoWorks 25 USA What is the Connection between them ? Canon 65 Japan Hitachi 15 Japan
Joins Join between Product and Company SELECT PName, Price FROM Product, Company WHERE Manufacturer=CName AND Country= Japan AND Price <= 200
Joins in SQL Product Company PName Price Category Manufacturer Cname StockPrice Country Gizmo $19.99 Gadgets GizmoWorks GizmoWorks 25 USA Powergizmo $29.99 Gadgets GizmoWorks Canon 65 Japan SingleTouch $149.99 Photography Canon Hitachi 15 Japan MultiTouch $203.99 Household Hitachi SELECT PName, Price FROM Product, Company WHERE Manufacturer=CName AND Country= Japan AND Price <= 200 PName Price SingleTouch $149.99
Joins Product (pname, price, category, manufacturer) Company (cname, stockPrice, country). Find all countries that manufacture some product in the Gadgets category. SELECT Country FROM Product, Company WHERE Manufacturer=CName AND Category= Gadgets
Joins in SQL Product Company PName Price Category Manufacturer Cname StockPrice Country Gizmo $19.99 Gadgets GizmoWorks GizmoWorks 25 USA Powergizmo $29.99 Gadgets GizmoWorks Canon 65 Japan SingleTouch $149.99 Photography Canon Hitachi 15 Japan MultiTouch $203.99 Household Hitachi SELECT Country FROM Product, Company WHERE Manufacturer=CName AND Category= Gadgets Country What is the problem ? What s the solution ? ?? ??
Joins Product (pname, price, category, manufacturer) Purchase (buyer, seller, store, product) Person(persname, phoneNumber, city) Find names of people living in Seattle that bought some product in the Gadgets category, and the names of the stores they bought such product from SELECT DISTINCT persname, store FROM Person, Purchase, Product WHERE persname=buyer AND product = pname AND city= Seattle AND category= Gadgets
Disambiguating Attributes Sometimes two relations have the same attribute: Person(pname, address, worksfor) Company(cname, address) Which address ? SELECT DISTINCT pname, address FROM Person, Company WHERE worksfor = cname SELECT DISTINCT Person.pname, Company.address FROM Person, Company WHERE Person.worksfor = Company.cname
Tuple Variables Product (pname, price, category, manufacturer) Purchase (buyer, seller, store, product) Person(persname, phoneNumber, city) Find all stores that sold at least one product that the store BestBuy also sold: SELECT DISTINCT x.store FROM Purchase AS x, Purchase AS y WHERE x.product = y.product AND y.store = BestBuy Answer (store)
Tuple Variables General rule: tuple variables introduced automatically by the system: Product ( name, price, category, manufacturer) SELECT name FROM Product WHERE price > 100 SELECT Product.name FROM Product AS Product WHERE Product.price > 100 Becomes: Doesn t work when Product occurs more than once: In that case the user needs to define variables explicitely.
Renaming Columns PName Price Category Manufacturer Product Gizmo $19.99 Gadgets GizmoWorks Powergizmo $29.99 Gadgets GizmoWorks SingleTouch $149.99 Photography Canon MultiTouch $203.99 Household Hitachi SELECT Pname AS prodName, Price AS askPrice FROM Product WHERE Price > 100 prodName askPrice SingleTouch $149.99 Query with renaming MultiTouch $203.99
